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.

The Solution

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/