cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
systamper Member
Member

Cumulative Totals Within Categories

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 personAccountSales Amount
JohnJ1100
JohnJ2200
JohnJ3300
JohnJ4100
JohnJ5200
HarrietH1300
HarrietH2100
HarrietH3200
HarrietH4300

 

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

Sales personAccountSales AmountCumulative
JohnJ110011%
JohnJ220033%
JohnJ330067%
JohnJ410078%
JohnJ5200100%

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cumulative Totals Within Categories

@systamper

 

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] )
    )
)
4 REPLIES 4
Super User
Super User

Re: Cumulative Totals Within Categories

@systamper

 

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] )
    )
)
Super User
Super User

Re: Cumulative Totals Within Categories

Super User
Super User

Re: Cumulative Totals Within Categories

@systamper

 

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] )
    )
)
systamper Member
Member

Re: Cumulative Totals Within Categories

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

 

Best,

Scott