Power BI: Need Help Creating Quarterly Rank and Quartiles

Question

I need help creating quartiles in Power BI based on a quarterly rank, but I don't have the ranking measure yet. I want to:

Create a measure that ranks my sales data each quarter
Divide my data into four quartiles (Quartile 1 being the top, Quartile 4 the bottom) based on this newly created quarterly rank.
Display the average of the ranked numerical field for each quartile in separate card visuals above a table. The table should show the detailed data, including the quarter, the numerical field, the quarterly rank, and the quartile assignment.

https://www.dropbox.com/scl/fi/2sl3smvho66hzikdmjath/Need-Help-Quartiles.pbix?rlkey=hz8yuj76r62oz07d1smr283xw&st=nyu3do10&dl=0

sirhawk3017 · Accepted Answer

tried this formula and seemed to work;&nbsp;&nbsp;Quartile =VAR RankedTable =&nbsp; &nbsp; ADDCOLUMNS(&nbsp; &nbsp; &nbsp; &nbsp; ALL(Medic_Info),&nbsp; &nbsp; &nbsp; &nbsp; "Rank", [FM Sales QTD Rank] &nbsp;// Add a temporary rank column&nbsp; &nbsp; )VAR MaxRank = MAXX(RankedTable, [Rank]) &nbsp;// Get the max rank from the temporary tableVAR QuartileSize = CEILING(MaxRank / 4, 1)RETURN&nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; [FM Sales QTD Rank] &lt;= QuartileSize, 1,&nbsp; &nbsp; &nbsp; &nbsp; [FM Sales QTD Rank] &lt;= QuartileSize * 2, 2,&nbsp; &nbsp; &nbsp; &nbsp; [FM Sales QTD Rank] &lt;= QuartileSize * 3, 3,&nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; )

Forum Discussion

Power BI: Need Help Creating Quarterly Rank and Quartiles

2 Replies

Related content

quartile ranking

Creating a Column to categorize by quartiles

Creating Slicers for Monthly & Quarterly Data

Quartiles Calculated Columns

Quartiles and average per quartile