Today we are going to focus on a technique used in the ‘Time Intelligence’ demo in the ‘Scenarios’ section of the Enterprise DNA showcase.
Being able to show dynamically the top or bottom outcomes of any calculations we do, say for instance sales, profits, margins, costs etc. is a great way to highlight key business outcomes.
Maybe you need to know where to invest more resource or understand which products to expand, or even if you need to shutdown poor performing stores or products. This is the perfect type of analysis for these sort of scenarios.
In the Time Intelligence demo, we have done just that. I’m looking to see which are our best performing and worse performing store locations by profit margins.
I could ask my regional managers or sales reps some good questions here. What’s great is the dynamic way I can move through each product and channel and see how our margins shape up across those dimensions. I can even change the time frame as well, which means you can identify trends over time for profit margins, or for any calculations you intend to make for that matter.
So how do we do this. There’s a bit to it but let’s run through it step by step. In this example we use a combination of the RANKX function and a tricky IF statement. In combination, you can start completing some game-changing analysis. This is also a pattern of analysis. You can re-use this time and time again.
First of all, we have to create our initial calculation. The one that we want to rank by and then filter to only receive the top or bottom results. In this case it is ‘Avgerage Margins per Day’.
Here we are running an iteration over our ‘Profit Margin’ measures. By using VALUES we create ann iteration over every single day within the current filter context. The current filter context in our case will eventually by ‘Regions’. So this stand-alone calculation will give us the average profit margin per day for each region.
Remember the context can also be adjusted by whichever time frame we have selected as well. If we only have one Month or Quarter selected the iteration only works over each day within that time frame.
So, now we have our initial calculation we can now move onto the next phase…ranking. For this we use RANKX.
First of all focus on the RANKX section within the IF statement. We first give RANKX a table, we use ALL( Regions ). We have to use ALL here because the current filter context is already being filtered by each individual region. If you don’t use ALL every region will be ranked first place. Remember this as you’ll be scratching your head for a while when you first use this function.
Remember this as you’ll be scratching your head for a while when you first use this function.
This RANKX function now gives use a rank for each individual region based on the parameter we give it. In this case, we used our ‘Avg Margins per Day’ measure, so that’s what we ranked on.
The IF statement wrapped around the RANKX function is just to check that you are using this against a ‘Regions’ context. Using HASONEVALUE is a great technique to use, so that you or future users don’t get tripped up using this calculation in a different context and getting weird results.
Now we move onto isolating only the top or bottom ranked regions. To do this we need to take advantage of the inner workings of DAX.
When DAX returns a result that is blank, as in there is no result, then just like when you use pivot tables nothing appears.
So we can look to almost trick our calculation to return blank and therefore receive no results. Check out the inner IF statement in the formula above. I only want to retrieve the top 7 results. So I have said that IF the ‘Top City Profit Margin Rank’ result is less than or equal to 7 then show me the ‘Avg Margins per Day’ result. If not return BLANK().
This is a really cool technique as now all that will be returned in the Top 7 results with their resulting Regions.
Now to get the bottom results!
It’s not too hard, trust me. There a bit to the below formula which at this point I won’t digress into. But really all you have to do here is instead of ranking the results in descending order like we did for the top results, all you need to do is rank them in ascending order.
And then again we use our tricky IF statement to only return the bottom 7 results.
There’s a few things within these formulas that we didn’t touch on explicitly today. Hopefully though that gives you the framework in which to use for ranking results. Remember this is a reusable technique that when shown dynamically in a report can bring immense life to a piece of analysis you may be running.
Good luck with it.