A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
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
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
RoundWeightage:=IF(HASONEVALUE(Round[RoundScore]), MAX(Round[Weightage]), BLANK())
, [RoundWeightage]* [ScoreTotal] )
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 http://www.ravishingwomen.org/