Facing problem with ABC Analysis. In the last two rows I am getting blank value for ABC Analysis

[euro_sree]

Hi Friends

I am facing a problem when doing the ABC analysis. In the last two records I am not getting no value for ABC analysis. In the last two records the cost of the part is same therefore the RANK has tied up.

My DAX formulas are as below

PartRank = RankX(all(PartInfo),PartInfo[Calculated_PartCost],,Desc,Dense)
CumulativeCost = CALCULATE([TotalMtlCost],filter(PartInfo,PartInfo[PartRank]<=EARLIER(PartInfo[PartRank],1)))
CumulativeCostPercentage = Divide(PartInfo[CumulativeCost],[TotalCostWOFilter],0)
ABC Analysis = Switch(
True(),
PartInfo[CumulativeCostPercentage]<= .40,"A",
PartInfo[CumulativeCostPercentage]<= .70,"B",
PartInfo[CumulativeCostPercentage]<= 1,"C"
)
Could any body suggest what the mistake is. The below are the last few records of the table

Part_PartNum

Part_PartDescription

Part_ClassID

Part_IUM

Part_QtyBearing

Part_CreatedOn

Part_CreatedBy

PartPlant_BackFlush

PartBin_OnhandQty

Calculated_PartCost

Calculated_LastTranDate

Part_TrackLots

PartBin_LotNum

PartRank

CumulativeCost

CumulativeCostPercentage

CostPercent

ABC Analysis

PRHN06DM_SS304

Nut Ss304

RMHW

EA

True

22-Dec-22

manager

False

13

6.5

06-Nov-24

False

839

7635390.8235570

99.9997650%

0.00%

C

PRHW12PL_SS316

Plain Washer SS 316

RMHW

EA

True

10-Jan-25

PU02

False

20

5.944

07-Feb-25

False

840

7635396.7675570

99.9998428%

0.00%

C

PCSPG0059

Tape

PGNC

EA

True

25-Mar-22

manager

False

6

4.8

14-Feb-25

False

841

7635401.5675570

99.9999057%

0.00%

C

PRHS10025SA_SS304

Allen socket head Screw SS 304

RMHW

EA

True

25-Mar-22

manager

False

4

3.6

06-Dec-24

False

842

7635408.7675570

100.0000000%

0.00%

PRHS04016PS_SS304

Pan head screw SS 304

RMHW

EA

True

08-Jun-23

PU01

False

15

3.6

06-Dec-24

False

842

7635408.7675570

100.0000000%

0.00%

[Anonymous]

Hi @euro_sree ,

I tried your DAX and it works fine.

ABC Analysis = 
SWITCH(
    TRUE(),
    PartInfo[CumulativeCostPercentage] <= 0.40, "A",
    PartInfo[CumulativeCostPercentage] <= 0.70, "B",
    PartInfo[CumulativeCostPercentage] <= 1, "C"
)

Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

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. 

[euro_sree]

@bhanu_gautam  Thanks for your response. I tried the code given by you

PartRank = RANKX(ALL(PartInfo),PartInfo[Calculated_PartCost] + (PartInfo[Part_PartNum] * 1e-10),,DESC,DENSE)

This did not work as my partnum is alpha numeric so it does not accept the expresssion PartNum *1e-10. Instead of that I tried PartRank = RANKX(ALL(PartInfo),Concatenate(PartInfo[Calculated_PartCost],PartInfo[Part_PartNum]),,DESC,DENSE). In this I am getting the rank without repeating but the ranks are wrong and its not in the descending order of part cost. In the last row of ABC analysis I am also not get the value.

[Anonymous]

Hi @euro_sree ,

Whether the advice given by bhanu_gautam has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[bhanu_gautam]

@euro_sree Modify the PartRank calculation to include a secondary column for tie-breaking:
PartRank = RANKX(
ALL(PartInfo),
PartInfo[Calculated_PartCost] + (PartInfo[Part_PartNum] * 1e-10),
,
DESC,
DENSE
)

Ensure the CumulativeCost and CumulativeCostPercentage calculations remain the same:
CumulativeCost = CALCULATE(
[TotalMtlCost],
FILTER(PartInfo, PartInfo[PartRank] <= EARLIER(PartInfo[PartRank], 1))
)

CumulativeCostPercentage = DIVIDE(
PartInfo[CumulativeCost],
[TotalCostWOFilter],
0
)

The ABC Analysis calculation remains the same:
ABC Analysis = SWITCH(
TRUE(),
PartInfo[CumulativeCostPercentage] <= 0.40, "A",
PartInfo[CumulativeCostPercentage] <= 0.70, "B",
PartInfo[CumulativeCostPercentage] <= 1, "C"
)
By adding a small value based on Part_PartNum to the Calculated_PartCost in the PartRank calculation, you ensure that each part gets a unique rank even if the costs are the same. This should resolve the issue with the last two records not getting an ABC analysis value.