HomeChallengesWhich States are performing below average?
learnbook poster
Which States are performing below average?Last updated at Tue Feb 15 2022Skills
data-wrangling
Tools
power-bi

Learning Outcomes


  • Learn how to use DAX in Power BI to indicate above average or below average performances in a visual

Problem Statement


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 :

img-01.png

Download the starter workbook here

How would you go about solving this case?

Upload your work here

My Solution


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 :

Watch Solution

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 :

img-01.png

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!


Created with 💙 by
author avatar
Amit Choudhary
Data Practitioner,Mentorskool
mentorskool logo
Mentee Today, Mentor Tomorrow
No 206, A Block, Sonesta Silver Oak,Varthur, Bangalore 560066,Karnataka
Copyright - 2022 © Mentorskool - All rights reserved.