cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
voleshko Frequent Visitor
Frequent Visitor

ABC Analyse; cumulative sum and percentage

I have a data table like:
Order        #Line                 #Item             Year              Qty

ALineA1car20182
ALineA2bus20184
BLineB1bike20186
CLineC1boots201811
CLineC2boots201811
DLineD1bus20184
ELineE1car20192
ELineE2car20192
FLineF1bus20193
FLineF2car20191


I need to create a calculated column with ABC classes. It has to be calculated column (NOT measure) because It will be used for the slicer.
For calculating need to calc cumulative SUM and I have a problem here.
How I see that.
First of all, have to calc Qty grouped by YEAR and ITEM.
Looks like:
Item                     Year               SUM(Qty) by YEAR and ITEM

car20195
bus20193
boots201822
bus20188
bike20186
car20182

Explanations
For 2018 bus, Qty=8 because has 2 lines with 4 pieces.
For 2019 Car, Qty=5 because has 3 lines: 2+2+1.
After that, I added TOTAL SUM ('data table'[Qty]) group by Year and tried to calc Cumulative SUM ordered by Year and SUM(Qty) with DAX:
Cumul TEST = CALCULATE (SUM ('data table'[Qty]),
FILTER (ALL('data table'),
'data table'[year] = EARLIER('data table'[year]) ), 'data table'[Qty] >= EARLIER('data tablea'[Qty])   )
but it doesn't work correctly.
Next step is to divide Cumulative SUM to TOTAL SUM ('data table'[Qty])
I think it can look like:

ItemYearSUM(Qty) by YEAR and ITEMCumulative SumTotal SumCumulative SUM/Total SumABC classes
car20195580.625A
bus20193881C
boots20182222380.578947368A
bus2018830380.789473684B
bike2018636380.947368421C
car2018238381C

If some value <=0.7 I say it is class A, <=0.9, class B and other class C.
And my expected result is a data table with ABC classes which I will use for the slicer.

Order #Line #ItemYearQtyABC classes
ALineA1car20182C
ALineA2bus20184B
BLineB1bike20186C
CLineC1boots201811A
CLineC2boots201811A
DLineD1bus20184B
ELineE1car20192A
ELineE2car20192A
FLineF1bus20193C
FLineF2car20191A

additional question: Can I use for a slicer a few YEARs, and recalc automatically ABC classes? Or need to add an additional column?
Or how to do it?

Thanks a lot!

1 ACCEPTED SOLUTION

Accepted Solutions
Cmcmahan New Contributor
New Contributor

Re: ABC Analyse; cumulative sum and percentage

So were you able to figure out your issue, or do you still have questions?

 

So if the cumulative sum is in order by [SUM(Qty) by YEAR and ITEM], then you should create the summarized table like above, and then add a calculated column.  This will still leave your original table so you can do other analysis on data like [month].  

Create the summary table:

SummaryTable = SUMMARIZE('data table', [year], [#item], 
        "SUM of Qty" , SUM([Qty]), 
        "Total Sum", SUMX(ALLEXCEPT('data table', [year]), [Qty]) 
    )


Create the cumulative total column on this calculated table:

Cumulative Sum = SUMX(FILTER(ALLEXCEPT('SummaryTable', [year]), EARLIER([SUM of Qty])>=[SUM of Qty]),[SUM of Qty]) 

 

 

4 REPLIES 4
Cmcmahan New Contributor
New Contributor

Re: ABC Analyse; cumulative sum and percentage

Woo. A lot of information here.  Let's see if I can help with one part at a time.

First up, it seems like instead of a calculated column, you want a calculated summarized table.  This will allow you to have the data grouped by year and item, showing the total count, as well as the cumulative total by year.

 

SummaryTable = SUMMARIZE('data table', [year], [#item], 
"SUM of Qty" , SUM([Qty]),
"Cumulative Sum", 0, //Further clarifications needed to calculate this field. See below
"Total Sum", SUMX(ALLEXCEPT('data table', [year]), [Qty])
)

So the tricky bit here is getting the cumulative total.  How do you want to order the data for the cumulative total?  How does PBI know that the Item  "car" comes before the Item "bus"?  In your example, in 2018 the order is Car < Bus.  In 2019 the order is boots < bus< bike < car.  If the data is resorted, should the cumulative total calculate differently? You can either set up an index to order the data or use RANKX to determine an order for the rows, and use an expression similar to the following:

SUMX(FILTER(ALLEXCEPT('data table', [year]), EARLIER(RANKX(<EXPRESSION>))>=RANKX(<EXPRESSION>)),[Qty]) 

Once you have a cumulative total that is to your liking, you can create more calculated columns on this new table for the rest of your values:

Cumulative/Total = 'SummaryTable'[Cumulative Sum] / 'SummaryTable'[Total Sum]
ABC Class = SWITCH( TRUE(), 
    'SummaryTable'[Cumulative/Total] <= 0.7, "A",
    'SummaryTable'[Cumulative/Total] <= 0.9, "B",
    "C" )

As far as slicing based on Year, you should be able to do that normally and the data will display as expected.  Slicing based on year will just ignore/not display data that doesn't match the slicer selection.

 

And I think that's at least an attempt to answer each of your questions?

voleshko Frequent Visitor
Frequent Visitor

Re: ABC Analyse; cumulative sum and percentage

@Cmcmahan 

Thanks for your reply.
Actually, I use SUMMARIZE table but in addition, need to use more fields like [Month] for another analysis. This field gives extra rows and I don't know how to create Cumulative Sum (group by YEAR and ITEM).
---
So the tricky bit here is getting the cumulative total. How do you want to order the data for the cumulative total? How does PBI know that the Item "car" comes before the Item "bus"?
Answer: PBI doesn't know. The sorting needs to be by field SUM(Qty):

ItemYearSUM(Qty) by YEAR and ITEMCumulative Sum
car201955
bus201938
boots20182222
bus2018830
bike2018636
car2018238

The year 2019, "SUM(Qty) by YEAR and ITEM" for a car is larger than for a bus. So need to sum car+bus: 5+3.
The year 2018, boots Qty is larger than for a bus, buses Qty is larger than for a bike. 22+8+6.....

Sorry, I couldn't understand How to calc Cumulative Sum (((
BTW thanks!

Cmcmahan New Contributor
New Contributor

Re: ABC Analyse; cumulative sum and percentage

So were you able to figure out your issue, or do you still have questions?

 

So if the cumulative sum is in order by [SUM(Qty) by YEAR and ITEM], then you should create the summarized table like above, and then add a calculated column.  This will still leave your original table so you can do other analysis on data like [month].  

Create the summary table:

SummaryTable = SUMMARIZE('data table', [year], [#item], 
        "SUM of Qty" , SUM([Qty]), 
        "Total Sum", SUMX(ALLEXCEPT('data table', [year]), [Qty]) 
    )


Create the cumulative total column on this calculated table:

Cumulative Sum = SUMX(FILTER(ALLEXCEPT('SummaryTable', [year]), EARLIER([SUM of Qty])>=[SUM of Qty]),[SUM of Qty]) 

 

 

voleshko Frequent Visitor
Frequent Visitor

Re: ABC Analyse; cumulative sum and percentage

@Cmcmahan 
Thanks!
Don't know why, but 

"Total Sum", SUMX(ALLEXCEPT('data table', [year]), [Qty]) 

did not work for me...
 And I can't understand how does it work 

Cumulative Sum = SUMX(FILTER(ALLEXCEPT('SummaryTable', [year]), EARLIER([SUM of Qty])>=[SUM of Qty]),[SUM of Qty]) 

Anyway, I used your idea ))))
For CumulativeSum I created calc column

ABC CumulatedQty =CALCULATE( SUM(SUM of Qty),
filter(ALL(SummaryTable),SummaryTable[SUM of Qty]>=EARLIER(SummaryTable[SUM of Qty])
&& SummaryTable[Year] = EARLIER(SummaryTable[Year]) ))

 
Have a nice day!