The Problem
We had presented you with the real world exercise, which you could solve using Power Pivot and Power Query. Click to read that blog post or watch the video below introducing the exercise. We highly recommend, you take a crack at solving this yourself before going on to review our solution.
Click to Download Exercise File
The Solution
Click to Download Solution File
You can watch the video below which explains the solution or read on…
Step 1A: Get Your Tables Into Power Pivot
Power Pivot is the central engine in Power BI. As an engine, it uses your data as a fuel. The first thing we will do is to feed the beast.
For the Judges, Contestants, Rounds excel tables simply click the “Create Linked Table/Add to Data Model” button (Excel 2010/2013) to send these to Power Pivot. The round score tables are a little tricky and covered in the next step.
Step 1B: Send Round Scores to Power Pivot via Power Query
Power Query is a terrific tool to clean, shape and transform your data before sending it to Power Pivot. We will use Power Query to combine the score sheets by different judges and also to unpivot columns into rows.
Using Power Query to combine our Judges Scores into a single table
Step 2: Create Relationships in Power Pivot
This data set is fairly clean allowing us to easily connect our data (Score) and lookup tables (Judge, Round, Contestants).
Data and Lookup tables connected via relationships
Step 3: Define DAX Measures
Here are all the measures we defined
ScoreTotal:=SUM(Scoring[ScoreEntered])
RoundWeightage:=IF(HASONEVALUE(Round[RoundScore]), MAX(Round[Weightage]), BLANK())
ScoreWeighted:=SUMX(
VALUES(Round[RoundScore])
, [RoundWeightage]* [ScoreTotal]
)
Rank:=IF(ISBLANK([ScoreWeighted])
, BLANK()
,RANKX(ALLSELECTED(Contestant), [ScoreWeighted])
)
Here is a quick description of each:
– ScoreTotal: Thanks to the Power Query shaping this is a simple sum
– RoundWeightage: Simple returns the weightage for a selected Round
– ScoreWeighted: Uses SUMX to iterate through each round and calculated the weighted score (weightage x round score)
– Rank: Uses RANKX with ALLSELECTED to rank our contestants based on [ScoreWeighted]
Step 4: Build the Pivot Table
All the hard work is done. Now you can simply drag-and-drop columns and measures onto your Excel pivots to build the desired solution:
Hope you enjoyed this exercise. I certainly enjoyed working at the beauty pageant and leveraging Power BI to make it run smoother. Kudos to Ashish and Menaka for putting together a terrific show. Learn more at https://www.ravishingwomen.org/