- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- ABC Analyse; cumulative sum and percentage

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

voleshko

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-20-2019
10:16 AM

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Cmcmahan

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019
07:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-20-2019
10:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019
03:09 AM

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

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-24-2019
07:34 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2019
09:22 AM

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