Interaction between RANKX and TOPN for ABC ( Paretto ) Analysis

[Leandro_Filho]

Hi guys, I have been stuck with this and I need some help.

I am developing a visual to segment my data between two categories. One of them is the usual ABC analysis, where I need to identify where the total volume of my items reach 80% of the total volume. Those items will be labeled as A, then the next 15% (totalizing 95%) will be labeled as B and the rest of them are C. 

What I did was first - Used the Rankx function to organize my items by volume:

! Ranking =

var aux = rankx(ALLSELECTED(DEMANDMASTER[UNIT]),[! Vol%])

return

    if(Or([Volume] == 0, [Volume] == Blank()), Blank(), aux)

This ranked my items perfectly. Then, I used the TOPN function to acumulate the values, line by line:

! Cumulative % = CALCULATE([Volume],
TOPN([! Ranking],ALLSELECTED(DemandMaster[UNIT]),
[Volume] , DESC))

/ CALCULATE([Volume], ALLSELECTED(DemandMaster[UNIT]))

I just divided by the total volume at the end so I could get the (% of total). It worked perfectly fine. The problem started when the leadership of my company requested me to add this info in a table with extra information in each row (such as product name, product group, etc). The moment I added new info in the table, The ranking went bananas ( got 1 in every row). Then I learned that the Rankx() function is a pain and you need to add these columns there as well. So I recreated the ranking measure:

! Ranking =

var aux =

Rankx(

    ALLSELECTED(DemandMaster[UNIT],

        DemandMaster[Global Planning Group Filter],

        DemandMaster[UNITDESCR],

        DemandMaster[UDC_GRWTH_PF],

        DemandMaster[UDC_GRWTH_SUB_PF],

        DemandMaster[UDC_PRODUCT_GROUP1_DESCR],

        DemandMaster[UDC_PRODUCT_GROUP2_DESCR],

        DemandMaster[UDC_CORE_RAW_MATER_DESCR]

        ),

    [Volume]

)

return

    if([Volume] = blank(), blank(),aux)

This worked well:

Then, I just need the cumulative number of the volume based on the ranking. The original cumulative that I described earlier is giving me just hte volume of each row. so I redid it adding the new columns in the ALLSELECTED() as well:
Cumulative = 

CALCULATE([Volume],

    TOPN(3,

        ALLSELECTED(BY_DemandMaster[UNIT],

        DemandMaster[Global Planning Group Filter],

        DemandMaster[UNITDESCR],

        DemandMaster[UDC_GRWTH_PF],

        DemandMaster[UDC_GRWTH_SUB_PF],

        DemandMaster[UDC_PRODUCT_GROUP1_DESCR],

        DemandMaster[UDC_PRODUCT_GROUP2_DESCR],

        DemandMaster[UDC_CORE_RAW_MATER_DESCR]

    ), [Volume], DESC )  

)
When I use a constant number - Like 3 in the formula above - for the first parameter of the TOPN() function, it calculates the volume of the 03 top volume itens perfectly.

But when I use the Ranking measure i described earlier, it gets me the volume of the row item its being calculated (?).

Which is weird, because if the Ranking was somehow returning 1 (its being calculated correctly for each row, as you guys can see in the images), the cumulative should return the value of the top1 item, not the volume of the item in the row (I tested using 1 as the first parameter n the TOPN() and this is exactly what happens).  I have been stuck with this for so long I feel I cant find an answer, i am about to cry 😞 

Can someone help me with some insights of what might be happening, I feel like its a context problem but I just cant understand.

[Anonymous]

Hi @Leandro_Filho ,

Rankx function, it ‘Returns the rank of an expression in the current context in the list of values for the expression evaluated for each row in the specified table’. 

Please try changing ALLSELECTED to ALL in the measure.

If you want to sort the cumulative volume, then use the sumx function.

I recommend that you refer to the following links to learn how to use the RANKX function in the TOPN function:

Power BI Pro Tip: Pareto analysis with DAX / Power Pivot · Dutch Data Dude

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.