WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 479 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

A technique to group measures together so they can be added with one click
If you've worked with date measures (or any others) in DAX for a while, you'll know they can clutter up your pivot table. This clever technique allows you to tick a single box to add lots of measures into a pivot table in one go.

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 08 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# A way to group date-based measures into a single field

It's all too easy in SSAS Tabular to suffer from measure overload:

A typical set of date-based measures created in the course of setting up a tabular model.

Wouldn't it be nice if you could group these together into a single dimension, which you could drag on to your pivot table?

In this utopian world, ticking the Sales column would add a range of measures to your pivot table.

Read on to discover how to turn this utopian vision into reality!

## The measures for this blog

For this blog, we'll assume that you have the following measures set up:

Although it doesn't really matter for the purposes of this blog what these measures do, the formulae are given below.

The formulae for the measures are:

Measure Formulae
TotalQuantity =SUM([Quantity])
Year to date =CALCULATE(
SUM([Quantity]),
DATESYTD(Calendar[Date])
)
Quarter to date =TOTALQTD(
sum([Quantity]),
Calendar[Date])

## Creating a statistics dimension

The first thing to do is to create a dimension including the statistics on which you want to report (if you want to follow this, you can import this Excel workbook).

You should hide the Id column from client view.

Note that you shouldn't link this table to any other (no relationships are needed).

To ensure that the statistics appear in the required order, sort them by the Id column you've just hidden:

Sort the Statistic column by the Id one.

## Creating the pivot table

You can now create what will be a blank pivot table, displaying the Statistic dimension across the top:

The fields for the pivot table (as yet, it has no measures).

Here's what this gives:

Now if only these cells contained appropriate numbers ... !

## Creating the measure to make it all work

Here's the measure to give the correct results for each column of the pivot table:

Sales:=IF (

HASONEVALUE ( Statistics[Statistic] ),

SWITCH (

VALUES ( Statistics[Statistic] ),

"Now", [TotalQuantity],

"Year-to-date", [Year to date],

"Quarter-to-date", [Quarter to date],

BLANK ()

),

BLANK ()

)

The outer part of this function tests to see whether the Statistics table has been included once in your pivot table (this should always be the case). If this isn't the case, the formula returns blank.

The inner part of the function looks for each pivot table cell at the value of the period being displayed, and does this calculation:

Statistics value being displayed Pivot table calculates and shows
Now [TotalQuantity]
Year-to-date [Year to date]
Quarter-to-date [Quarter to date]
Anything else Blank

## Adding this measure to your pivot table

Finally, you can display this measure!

By hiding all of the measures apart from the Sales one just created you can hugely simplify the pivot table field list.

And here's the final pivot table in all its glory:

The 3 statistics shown side-by-side.

This approach is obviously most suited to situations where you're not going to have to change the set of measures displayed frequently!

This blog has 0 threads