Case Study - Top Quartile Reporting in Power BI using the PERCENTILE.INC() DAX Function

Learn how to set up a Power BI column chart that calculates the top quartile performers in a given data set.
I'll cover 2 approaches. The first is a quick and easy method using the Power BI Analytics features.
In the second approach, which is more flexible, I'll use the PERCENTILEX.INC() function to calculate the top quartile performers.
I'll also explain how to set up a neat feature that allows you to select the top, middle, or bottom quartile performers and dynamically highlight these on the column chart. It highlights the selected quartile by changing the colour of the columns, while also displaying a percentile threshold line.
👍Tip: This is a longer video so try increasing the playback speed to speed up your learning.
----------------------------------
⏩ Video Navigation
----------------------------------
00:00 - Introduction
00:52 - What is the 'Top Quartile'?
04:10 - How a Percentile Value is Calculated?
06:08 - Quick Approach - Adding a Top Quartile Line Using Power BI Analytics Feature
09:44 - Requirements for Dynamic Conditional Formatting of Top Quartile
11:03 - Overview of PERCENTILEX.INC()
14:02 - Creating the Percentile Calculation Measure using PERCENTILEX.INC()
19:45 - Dynamically Highlighting TQ - Step 1: Create a Selection Slicer
23:25 - Dynamically Highlighting TQ - Step 2: Link Selection Slicer to Percentile Measure
25:00 - Dynamically Highlighting TQ - Step 3: Create Column Colour Conditional Formatting Measure
37:34 - Adding a Dynamic Chart Header That Includes Percentile Threshold Value
42:03 - Summary of Video
-----------------------------------------------------
🎥 Related Videos
-----------------------------------------------------
QT#21 - Stop using IF(), use SWITCH()
• QT#21 - Stop using IF(...
-----------------------------------------------------
🎓 Power BI Dashboard Training
-----------------------------------------------------
👉FREE Power BI Quick Start Course - Learn how to create your first dashboard in around 90 minutes!
effectivedashboards.com/power...
👉 Power BI Quick Start PLUS - Learn how to craft KPI management dashboards that really do help you, your boss, and your peers make better decisions!
effectivedashboards.com/power...
-------------------------
📁 Resources
-------------------------
👉Access to the source PBIX files for my TH-cam videos:
effectivedashboards.com/cours...
👉Article series on creating a maintenance management system data model and how to address the human factors of data collection for improved data quality. (This is not just relevant to maintenance and reliability data).
effectivedashboards.com/data-...

[@andrekroker420]

The storytelling behind this is just fantastic. The way you described each step was VERY clear. Thanks a lot for sharing this Jason!

[@jasondavidsondata]

Thanks André, I appreciate your kind comments and it's great you found it useful.👍😀

[@felixthecatx887]

This is great. I'm new to Power Bi and you explained the scenario really well.

[@jasondavidsondata]

Hi Chris, Thanks for your comment. I'm happy you found it useful. Cheers Jason

[@UlyssesHaq]

Im getting ready to use this technique for a job interview presentation! Eternally indebted to you. Thank you for sharing, love this channel. Cool, calm and data driven!

[@jasondavidsondata]

Amazing, Hope you get the job!

[@UlyssesHaq]

@@jasondavidsondata thank you kindly, job secured! Moving up the PBI career. Thank you for all you do. You are positively improving peoples lives.

[@jasondavidsondata]

@@UlyssesHaq Thats amazing news! Well done! 🥳

[@tinaflemons263]

Awesome approach

[@jasondavidsondata]

Thanks Tina, glade you found it useful.

[@marianopitre]

Very useful, thanks!

[@jasondavidsondata]

Cheers for the comment. Glad you found it useful 👍 😀

[@SageSam]

Hey dude, question for you. I tried running this on a very similar data set. I have employee names instead of companies and hours of labor instead of PE%. When I get to the conditional formatting measure, it defaults to general format, but cannot be switched to text - the drop down only gives me numerical options. When I go to select the measure in the conditional format field it is greyed out (only the text measures are not greyed out, which is why I assumed its a formatting issue). Any tips?

[@karlangeldilag4370]

It's showing 100% on my end when using dax. I think the problem is because my RAW Data consists of 1 or 0.. it's a Survey data where 1=Good 0=Bad. How can I make this work in dax?

[@joshblackman2380]

This is very close to what I need to accomplish but instead of ranking a column I need to rank a measure instead (profit / unit). Is there a way to adapt this pattern to make that work? Thanks again for the lesson.

[@jasondavidsondata]

Hi Josh, I'll need to check back on the video but it should work for a measure too. Did it not work for yiu when you tried it?

[@teddyfabriciocordovasaenz3018]

​@@jasondavidsondata I didn't work when ranking measures, i have not found any video on all youtube