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

## ABC Analyse; cumulative sum and percentage

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

 A LineA1 car 2018 2 A LineA2 bus 2018 4 B LineB1 bike 2018 6 C LineC1 boots 2018 11 C LineC2 boots 2018 11 D LineD1 bus 2018 4 E LineE1 car 2019 2 E LineE2 car 2019 2 F LineF1 bus 2019 3 F LineF2 car 2019 1

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

 car 2019 5 bus 2019 3 boots 2018 22 bus 2018 8 bike 2018 6 car 2018 2

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:

 Item Year SUM(Qty) by YEAR and ITEM Cumulative Sum Total Sum Cumulative SUM/Total Sum ABC classes car 2019 5 5 8 0.625 A bus 2019 3 8 8 1 C boots 2018 22 22 38 0.578947368 A bus 2018 8 30 38 0.789473684 B bike 2018 6 36 38 0.947368421 C car 2018 2 38 38 1 C

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 # Item Year Qty ABC classes A LineA1 car 2018 2 C A LineA2 bus 2018 4 B B LineB1 bike 2018 6 C C LineC1 boots 2018 11 A C LineC2 boots 2018 11 A D LineD1 bus 2018 4 B E LineE1 car 2019 2 A E LineE2 car 2019 2 A F LineF1 bus 2019 3 C F LineF2 car 2019 1 A

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

## 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

## 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

## Re: ABC Analyse; cumulative sum and percentage

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):

 Item Year SUM(Qty) by YEAR and ITEM Cumulative Sum car 2019 5 5 bus 2019 3 8 boots 2018 22 22 bus 2018 8 30 bike 2018 6 36 car 2018 2 38

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

## 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

## 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!