BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!