Here is another great tip that was submitted by my colleague Edwin Commandeur from Dimensional Insight in the Netherlands, in response to a query from one of his customers. Thanks Edwin – it’s great to build up a library of techniques like this one!
Edwin’s customer wanted to know how many products had exceeded a particular threshold for Gross Margin Value, which was easy to do at the product level but not so easy to manage at any level of hierarchy or other dimension above that. This is because the condition would normally be met at the higher level, not at the granular level, thus obscuring what he wanted to know. Since the condition is dynamic, it is not possible to determine if the condition is met beforehand.
So he set about creating a few calculations that allowed his customer to dive at any level but still see the count relating to a dynamic condition being met at the granular product level.
You can implement his technique combining it with Time Series or any other quickviews as well – to get even more context around your analysis. Of course you can use this method with any data names and models, just chose dimensions that are in some sort of hierarchy.
Here is an example using the demo_drs.mdl data.
Follow these steps for an example that implements the dynamic condition using a free entry quickview:
CLICK ON THE IMAGES TO SEE FULL SIZE
1. First dive on Product Name and calculate Gross Profit as follows )
Total[Revenue] – Total[Cost]
2. Create a free entry quickview called Threshold and set its default value at 0.
3.Create a conditional calculation that yields 1 if the condition is met and 0 if the condition is not met. For the purposes of this example we create a calculation called Gross Profit Above Threshold that is defined as follows:
if(calc[Gross profit] > $(Threshold), 1, 0)
4. Close dive window for dive on Product Name and dive on Sales Region.
5. Calculate # Products Above Threshold as follows:
DimAverage[Product Name, calc[Gross Profit Above Threshold]] * dimcount[Product Name]
6. Verify your results by changing your Threshold in the Quickview to something with few chances of meeting the condition, (in this case we chose 5,000), then dive from Sales Region to Product Name and sort by Gross Profit Above Threshold. You will see easily that the number of products matches the number at Sales Region Level.