Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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] ) ) )
@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] ) ) )
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
Isn't the Account column text? Does <= work with text fields?
Thanks so much for your help on this, @Zubair_Muhammad! Can't tell you how much I appreciate it.
Best,
Scott
@Anonymous
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] ) ) )
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |