- Learn how to use DAX in Power BI to indicate above average or below average performances in a visual
You have been running your e-commerce business across the US. During the annual business review, you would like to know which states have sold better than the national average and which ones below the national average.
Why is this insight important?
This would help find out which are the top states to focus on offer better discounts, run loyalty programs etc. Have a look at the expected outcome below :
Download the starter workbook here
How would you go about solving this case?
Upload your work here
Let me mention upfront that although I solved this particular case, I still don't consider it the best possible solution.
But hold on! Did you make an attempt to solve the case? I'd strongly suggest to make a submission above and then look at my solution.
Ready to proceed? Just click on the triangle icon below :
Additional Challenge to try
All right! So, you are good with the skills needed to tackle the challenge above? Am happy that you learnt something today.
Why not try another variation of the challenge above.
This time, I would like to see for each state average city wise sales. How would you do that?
Think, Think! 🤔🤔
What could I do better?
Although the solution worked perfectly, consider the case where you are asked to build the same visual but now across Months or categories or rather any dimension. I should rather be able to change the dimension on the fly using a slicer option and the visual should vary dynamically.
My solution has this problem that the dimension is hard coded now (due to aggregated table). To create a visual with month in the dimension, I'll have to create another aggregated DAX table grouped by Month.
What if this aggregation and average calculation be completely dynamic?
Have a look at the expected outcome below :
Alas, I couldn't find a solution! Looking for someone who could help solve this case
Wish to discuss your solution and solve more interesting cases?
Join us on Slack today!