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
1	Group	Sub Group	Current Month 2015 Sales	Current Month 2014 Sales	Current Month YOY Sales Var	Current Month YOY Sales Var %	YOY Sales Growth Rank	% of Running Total	Top 80%?	Growth % Rank (Top 80% of PY Sales)
2	A	1	500	350	150	43%	6	29.0%	x	3
3	A	5	325	325	-	0%	9	55.9%	x	4
4	A	4	250	145	105	72%	4	67.9%	x	1
5	A	6	225	140	85	61%	5	79.5%	x	2
6	A	9	125	100	25	25%	7	87.8%
7	A	8	450	75	375	500%	2	94.0%
8	A	3	50	45	5	11%	8	97.8%
9	A	2	100	25	75	300%	3	99.8%
10	A	7	50	2	48	2400%	1	100.0%
11	Total		2,075	1,207	868	72%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

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

<tbody>
</tbody>

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!