ABC Analyse; cumulative sum and percentage

[voleshko]

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!

[Cmcmahan]

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]

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

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 · Accepted solution]

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]

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