I got this interesting question on my ask a questions page that I would like to answer by writing a blog post.
The question was the following:
I have a table fetched from a SQL database that contains a few fields but the interested ones are ComputerID and SystemStartupTime which is the time a computer takes to boot.
I have been asked to display this information on a graph averaged by the average of each ComputerID which I did like this:
The graph using this measure shows those values by Year and WeekNumber successfully using slicers to play with different variables.
Now I have to display this average on another graph by segments of 30 seconds. This means, by number of machines that booted in:
– 0 to 30 seconds
– 31 to 60 seconds
I wanted to use an autocalculated column. However I lose the average per computer (as the table lists all of the boot times).
I then tried to create a new measure with a long and dirty IF:
=if(Table[myWonderfulMeasure]<=30, “0-30”, if(Table[myWonderfulMeasure]<=60, “30-60”, [..] ))
However it doesn’t work at all. I can only use this measure as value (which displays nothing on the graph except 0, 0.1, .., 0.9, 1 on the y axe, and no bar) I can’t put the measure on the Legend Field for instance.
Unfortunately it is not possible to use a measure and put it on a label in Excel (but it is possible in Power View for non text meaures). So we need another solution. We can solve this in the exact opposite way. Lets look at an example using adventureworks with the FactInternetSales and DimProduct (filtered to only contain the current items) table:
I want to see the number of products per color per salesgroup where salesgroup is defined in Large, Medium and Small. The first idea that comes to mind is to create a measure that does what we want. That is pretty straightforward but unfortunately doesn’t work in Excel. So we need to come up with another solution.
What I did is define a table in Excel that defines the SalesGroup with Labels and min and max values:
Now I load this into PowerPivot as a linked table (no relationships created) and start by creating a pivottable using Color and the Label
Now I can create a measure that determines the number of products that fall between the min and max for each SalesGroup:
=if(HASONEVALUE(Categorygroup[Label]), COUNTROWS( FILTER(DimProduct, [Sum of SalesAmount] >= VALUES(Categorygroup[Low]) && [Sum of SalesAmount] < VALUES(Categorygroup[High]) ) ) , COUNTROWS(DimProduct))
This gives us the result we wanted:
or create a chart from it:
Lets look at this measure step by step:
- if(HASONEVALUE(categorygroup [Label]), … , COUNTROWS(DimProduct)): Do we have a single categorygroup selected in the pivottable ? If yes we can select a range, if more are selected return COUNTROWS(DimProduct)
- COUNTROWS(: Count the number of rows
- FILTER(DimProduct, : From a filtered DimProducttable (observe that the filter created by putting colors on rows in effect as well)
- [Sum of SalesAmount] >= values(categorygroup [Low])
&& [Sum of SalesAmount] < VALUES(categorygroup [High])) : Filter all rows in the DimProduct table where a measure [Sum of SalesAmount] is greater or equal then the value of categorygroup [Low] for the current categorygroup (using VALUES) and [Sum of SalesAmount] smaller then the value of categorygroup [High]
Hope this showed a simple approach to create a solution for Excel that gives you dynamic labels.