cancel
Showing results for
Did you mean:
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 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

1 ACCEPTED SOLUTION

Accepted Solutions
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

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

@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] )
)
)```
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