Introduction
The Power BI Workout Wednesday team has decided that May is made for DAX! Each challenge this month will require some DAX in the solution. For this week’s solution we are going to practice creating some tables in Power BI Desktop using DAX to help us answer some questions!
Did you know that you can use DAX to create tables as well as measures and columns? If you didn’t, get ready to have some fun! Our challenge dataset is going to be somewhat familiar if you’ve done some of my challenges before, it’s our handy dandy Superstore dataset. Using this datasets we’ll have some fun creating ad-hoc tables and answering some questions. Creating tables using DAX can be super helpful for validating results among other things.
Requirements
- Retrieve the data from data.world.
- Load the Orders table into Power BI Desktop
- Using DAX create the first table using DAX
-
- SUMMARIZE by Region, create fields for Sum of Sales, Total Sales (All in table), and % of totals sales by Region
-
- Using DAX create the second table using DAX
-
- SUMMARIZE by Region and Order Date Year, create fields for Sum of Sales, Total Sales (All in table), % of total sales by Region & Year, and % of sales by Region for each year
-
- Using DAX create the third table using DAX
-
- For only Year(Order Date) = 2019, SUMMARIZE by Region and Category, create fields for Sum of Sales, Total Sales (All in table), % of total sales by Region & Category, and % of sales for each Region by Category.
-
- Create 3 bar charts showing the % breakdowns
-
- Show % of total sales by Region
- Show each Region as a % of sales for each Year (Order Date)
- Show each Category as a % of total sales in each Region for 2019 (Order Date) only.
-
Dataset
This challenge uses data provided on data.world specifically for this exercise. You can use the data.world connector in Power BI (recommended) or download the Excel file to your local machine. You will need to log in to data.world to retrieve the data, but accounts are free.
Share
After you finish your workout, share on Twitter using the hashtags #WOW2021 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd and @dataveld. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
This was an incredible challenge, I used calculation instead of creating summarization.
Thanks for posting this challenge. It was very helpful in learning Filter, All, AllSelected functions. Keep posting.
Very informative challenge.
As per actual calculations, % of Sales by region in each year is showing incorrect.
For Example : Total Sales for Region = Central : 501239.89 and segregation of this sales year wise is as:-
2017 – 103838.16 (20.72%)
2018 – 102874.22 (20.52%)
2019 – 147429.38 (29.41%)
2020 – 147098.13 (29.35%)
But as per your calculations, % of year wise sales is as:-
2017 : 21.44 %
2018 : 21.86%
2019 : 24.20%
2020 : 20.06%
Please clear this confusion and suggest the correct solution for this.