Cumulative Totals Within Categories

[Anonymous]

Hi, everyone. 

I have what I thought would be a simple requirement to create a cumulative percentage across accounts and by sales person. 

Here is a simple version of my data: 

Sales person	Account	Sales Amount
John	J1	100
John	J2	200
John	J3	300
John	J4	100
John	J5	200
Harriet	H1	300
Harriet	H2	100
Harriet	H3	200
Harriet	H4	300

I am filtering my data down to a single sales person. Any my goal is to add a fourth column as follows:

Sales person	Account	Sales Amount	Cumulative
John	J1	100	11%
John	J2	200	33%
John	J3	300	67%
John	J4	100	78%
John	J5	200	100%

And then if I select Harriet, I would want to get an equivalent chart.

I tried the Running Total Quick Calculation, but it just returns the Sales Amount back to me. Any ideas or suggestions on this?

Thanks!

Scott

[Zubair_Muhammad · Accepted solution]

@Anonymous

As a calculated column, you could try

Cumulative =
DIVIDE (
    CALCULATE (
        SUM ( TableName[Sales Amount] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Sales person] ),
            TableName[Account] <= EARLIER ( TableName[Account] )
        )
    ),
    CALCULATE (
        SUM ( TableName[Sales Amount] ),
        ALLEXCEPT ( TableName, TableName[Sales person] )
    )
)

[hlanner]

Hello Zubair, could you help me with a similiar topic that I cannot find a solution for? I would like to calculate the average sales of last 3 month based on product and reseller. Appreciate your help

3 month avg sales

[Anonymous]

Isn't the Account column text? Does <= work with text fields?

[Anonymous]

Thanks so much for your help on this, @Zubair_Muhammad! Can't tell you how much I appreciate it. 

Best,

Scott

[Zubair_Muhammad]

@Anonymous

[adeeln]

Don't know how it's working strange for me.
Anyway here is the right solution

https://exceleratorbi.com.au/cumulative-running-total-based-on-highest-value/

[Zubair_Muhammad]

@Anonymous

If you need a MEASURE instead of a Calculated Column.....you just need to replace EARLIER with SELECTEDVALUE

i,e.

Cumulative_MEASURE =
DIVIDE (
    CALCULATE (
        SUM ( TableName[Sales Amount] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Sales person] ),
            TableName[Account] <= SELECTEDVALUE ( TableName[Account] )
        )
    ),
    CALCULATE (
        SUM ( TableName[Sales Amount] ),
        ALLEXCEPT ( TableName, TableName[Sales person] )
    )
)