Forum Discussion

Leandro_Filho's avatar
Leandro_Filho
New Member
3 years ago

Interaction between RANKX and TOPN for ABC ( Paretto ) Analysis

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.

1 Reply