Calculate cumulative sum of summarized table column

Question

I having trouble calculating the cumulative sum of a column on PowerBI.

I have a big offer table and I want to run a pareto analysis on it. Following many tutorials, I created a SUMMARIZED table by offer and a sum of their sales. So the table definition is:

summary = SUMMARIZE(big_table; big_table[offer]; "offer sales"; sum(big_table[sales]))

Many of the forums and stackoverflow answers I found have direct me to the following formula for cumulative sum on column:

cum_sales = 
    CALCULATE(
        sum([offer_sales]);
        FILTER(
            ALLSELECTED(summary);
            summary[offer_sales] <= max( summary[offer_sales])
        )
    )

However the resulting table is not correct:

sample results

What I need is simply to have the offers ordered by sales descending and then add the current row's sales amount to the previous row's sales,

So I excepted numbers closer to:

1st row: 1.5M
2nd row: 2.1M
3rd row: 2.6M and so on

But (maybe) because of my data structure and (certainly) lack of knowledge on how PowerBI works, I'm not getting the right results...

greggyb · Accepted Answer · 2019-08-30 17:50:50Z

2

Total Amount = SUM ( 'Fact'[Amount] )

Offer Visual Cumulative = 
VAR OfferSum =
    ADDCOLUMNS (
        ALLSELECTED ( 'Offer'[Offer] ),
        "amt", [Total Amount]
    )
VAR CurrentOfferAmount = [Total Amount]
VAR OffersLessThanCurrent =
    FILTER (
        OfferSum,
        [amt] <= CurrentOfferAmount
    )
RETURN
    SUMX (
        OffersLessThanCurrent,
        [amt]
    )

There's no need to pre-aggregate to a summary table. We can handle that as in the measure above.

This assumes a single fact table named 'Fact', and a table of distinct offers, 'Offer'.

Depending on what you're doing in terms of other filters on 'Offer', you may need to instead do as below:

Offer Visual Cumulative = 
VAR OfferSum =
    ADDCOLUMNS (
        ALLSELECTED ( 'Offer'[Offer] ),
        "amt", CALCULATE ( [Total Amount], ALLEXCEPT ( 'Offer', 'Offer'[Offer] ) )
    )
...

The rest of the measure would be the same.

The measure is fairly self-documenting in its VARs. The first VAR, OfferSum is a table with columns ('Offer'[Offer], [amt]). This will include all offers displayed in the current visual. CurrentOfferAmount is the amount for the offer on the current row/axis label of the visual. OffersLessThanCurrent takes OfferSum and filters it. Finally, we iterate OffersLessThanCurrent and add up the amounts.

Here's a sample:

answered Aug 30, 2019 at 17:50

greggyb

3,8281 gold badge13 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Original BBQ Sauce Over a year ago

I would like to thank you for such a carefully written answer! <3

Collectives™ on Stack Overflow

Calculate cumulative sum of summarized table column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related