Rank Top X% In A Pivot Using DAX

[Hopeless Cub Fan]

I am looking to create a DAX formula to rank just the top x% of sales $’s in a pivot table. The goal is to be able to filter out the records that do not hit the top x% threshold and then sort the remaining records based on their rank. I will be using this functionality to create a “Top Movers” (both positive and negative) report.

Below is a sample data set that illustrates how I am currently doing this ranking outside of the pivot.

Excel 2010

	A	B	C	D	E	F	G	H	I	J
A	x
A	x
A	x
A	x
A
A
A
A
A
Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Group[/TD]
[TD="align: center"]Sub Group[/TD]
[TD="align: center"]Current Month 2015 Sales[/TD]
[TD="align: center"]Current Month 2014 Sales[/TD]
[TD="align: center"]Current Month YOY Sales Var[/TD]
[TD="align: center"]Current Month YOY Sales Var %[/TD]
[TD="align: center"]YOY Sales Growth Rank[/TD]
[TD="align: center"]% of Running Total[/TD]
[TD="align: center"]Top 80%?[/TD]
[TD="align: center"]Growth % Rank (Top 80% of PY Sales)[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"] 500 [/TD]
[TD="align: right"] 350 [/TD]
[TD="align: right"] 150 [/TD]
[TD="bgcolor: #FFFF00, align: right"]43%[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]29.0%[/TD]

[TD="bgcolor: #FFFF00, align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"] 325 [/TD]
[TD="align: right"] 325 [/TD]
[TD="align: right"] - [/TD]
[TD="bgcolor: #FFFF00, align: right"]0%[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]55.9%[/TD]

[TD="bgcolor: #FFFF00, align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"] 250 [/TD]
[TD="align: right"] 145 [/TD]
[TD="align: right"] 105 [/TD]
[TD="bgcolor: #FFFF00, align: right"]72%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]67.9%[/TD]

[TD="bgcolor: #FFFF00, align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"] 225 [/TD]
[TD="align: right"] 140 [/TD]
[TD="align: right"] 85 [/TD]
[TD="bgcolor: #FFFF00, align: right"]61%[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]79.5%[/TD]

[TD="bgcolor: #FFFF00, align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"] 125 [/TD]
[TD="align: right"] 100 [/TD]
[TD="align: right"] 25 [/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]87.8%[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"] 450 [/TD]
[TD="align: right"] 75 [/TD]
[TD="align: right"] 375 [/TD]
[TD="align: right"]500%[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]94.0%[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"] 50 [/TD]
[TD="align: right"] 45 [/TD]
[TD="align: right"] 5 [/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]97.8%[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"] 100 [/TD]
[TD="align: right"] 25 [/TD]
[TD="align: right"] 75 [/TD]
[TD="align: right"]300%[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]99.8%[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"] 50 [/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"] 48 [/TD]
[TD="align: right"]2400%[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100.0%[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"] 2,075 [/TD]
[TD="align: right"] 1,207 [/TD]
[TD="align: right"] 868 [/TD]
[TD="align: right"]72%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=C2-D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=E2/D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=RANK(F2,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=SUM($D$2:D2)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I2[/TH]
[TD="align: left"]=IF(H2<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J2[/TH]
[TD="align: left"]=IF(I2<>"x","",SUMPRODUCT(--(F2<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=C3-D3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=E3/D3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=RANK(F3,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=SUM($D$2:D3)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=IF(H3<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=IF(I3<>"x","",SUMPRODUCT(--(F3<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=C4-D4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=E4/D4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=RANK(F4,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"]=SUM($D$2:D4)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"]=IF(H4<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J4[/TH]
[TD="align: left"]=IF(I4<>"x","",SUMPRODUCT(--(F4<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=C5-D5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]=E5/D5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]=RANK(F5,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H5[/TH]
[TD="align: left"]=SUM($D$2:D5)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I5[/TH]
[TD="align: left"]=IF(H5<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J5[/TH]
[TD="align: left"]=IF(I5<>"x","",SUMPRODUCT(--(F5<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E6[/TH]
[TD="align: left"]=C6-D6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]=E6/D6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G6[/TH]
[TD="align: left"]=RANK(F6,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H6[/TH]
[TD="align: left"]=SUM($D$2:D6)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I6[/TH]
[TD="align: left"]=IF(H6<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J6[/TH]
[TD="align: left"]=IF(I6<>"x","",SUMPRODUCT(--(F6<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=C7-D7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F7[/TH]
[TD="align: left"]=E7/D7[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G7[/TH]
[TD="align: left"]=RANK(F7,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H7[/TH]
[TD="align: left"]=SUM($D$2:D7)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I7[/TH]
[TD="align: left"]=IF(H7<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J7[/TH]
[TD="align: left"]=IF(I7<>"x","",SUMPRODUCT(--(F7<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=C8-D8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F8[/TH]
[TD="align: left"]=E8/D8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G8[/TH]
[TD="align: left"]=RANK(F8,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H8[/TH]
[TD="align: left"]=SUM($D$2:D8)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I8[/TH]
[TD="align: left"]=IF(H8<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J8[/TH]
[TD="align: left"]=IF(I8<>"x","",SUMPRODUCT(--(F8<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=C9-D9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"]=E9/D9[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G9[/TH]
[TD="align: left"]=RANK(F9,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H9[/TH]
[TD="align: left"]=SUM($D$2:D9)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I9[/TH]
[TD="align: left"]=IF(H9<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J9[/TH]
[TD="align: left"]=IF(I9<>"x","",SUMPRODUCT(--(F9<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E10[/TH]
[TD="align: left"]=C10-D10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F10[/TH]
[TD="align: left"]=E10/D10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G10[/TH]
[TD="align: left"]=RANK(F10,$F$2:$F$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H10[/TH]
[TD="align: left"]=SUM($D$2:D10)/$D$11[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I10[/TH]
[TD="align: left"]=IF(H10<=0.8,"x","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J10[/TH]
[TD="align: left"]=IF(I10<>"x","",SUMPRODUCT(--(F10<=$F$2:$F$10)*--($I$2:$I$10="x")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=SUM(C2:C10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D11[/TH]
[TD="align: left"]=SUM(D2:D10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E11[/TH]
[TD="align: left"]=SUM(E2:E10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F11[/TH]
[TD="align: left"]=E11/D11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Additional Notes:

• Ranking will be done off "Current Month YOY Sales Var %"
• Goal is to rank only the top X% of customers (in this example 80%) based off prior year sales. The last record in the example above shows ridiculious growth, but that is due to the low baseline (2).
• I have a DAX rank formula (below) that properly ranks fields, but has the following problems:
  • RANKX(FILTER(All(Table1[CRPCUSNM]),Table1[Current Month YOY Sales Var]),Table1[Current Month YOY Sales Var],,,Dense)
  • Ranks all "CRPCUSNM" values, not just the top 50% of sales. I've tried to restrict it, but can't get it to work.
  • Rank only works when "CRPCUSNM" is in the report. I would like to have a dynamic rank that adjusts based on the level of granularity in the pivot report without the need to reference a specific field.

Thanks in advance for any help you can offer!

 

[scottsen]

I am thinking you have ~3 options:

* write a calc colum that is "am I in the top N %?". Only workable if you don't have to change the definition of Top N based on slicers n such (now show me the top n in the NW Region...)

* have every measure on your pivot return NULL if the product is not in the Top N %... (ugh)

* Use the built in Value Filters of the pivot table...?

 

[Hopeless Cub Fan]

Thanks for the response. As you alluded to, option 1 won't work because I will be moving between regions. Option 2 could work, but it looks like option 3 is the best choice right now. I'll try it out and let you know how it goes. Thanks!