Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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 sales3 month avg sales

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Best,

Scott

@Anonymous

 

Cumulative%age.png


Regards
Zubair

Please try my custom visuals

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/

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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.