Forum Discussion
Incorrect cumulative values when creating multiple line charts with multiple cumulative values
I am trying to create cumulative sales vs goal line charts by person, team and group but am having a challenge with the calculation to cumulate the sales. Any help / ideas would be appreciated.
I have the two tables below:
PERSONAL GOAL - Notes the 12 month sales goal for an individual and which team they are on as well as which group they are in.
PERSONAL SALES - Notes the sales over the last 12 months for the individuals.
PERSONAL GOAL
| Person | Team | Group | Person Sales Goal |
| Sam | A | 1 | 100000 |
| John | A | 1 | 110000 |
| Jane | B | 1 | 120000 |
| Steve | B | 1 | 90000 |
| Susan | C | 2 | 130000 |
| Ann | C | 2 | 95000 |
PERSONAL SALES
| Person | Sales Month | Monthly Sales |
| Ann | 3 | 24900 |
| Ann | 8 | 37700 |
| Ann | 9 | 29900 |
| Jane | 5 | 41800 |
| Jane | 6 | 36400 |
| Jane | 12 | 19200 |
| John | 1 | 9800 |
| John | 5 | 5700 |
| John | 5 | 37200 |
| Sam | 2 | 24600 |
| Sam | 5 | 2000 |
| Sam | 10 | 12400 |
| Steve | 1 | 23700 |
| Steve | 8 | 26400 |
| Steve | 11 | 17000 |
| Susan | 1 | 2800 |
| Susan | 3 | 10800 |
| Susan | 12 | 20100 |
My problem is when I try to calculate the cumulative sales for each of the three different charts (person, team and group) the calculations don't seem to work.
I've put a "Person Index" on the table and cumulate the sales as follows:
Person Cumulative Sales = CALCULATE (
SUM ( 'Monthly Sales'[Monthly Sales] ),
ALLEXCEPT ( 'Monthly Sales', 'Monthly Sales'[Person] ),
'Monthly Sales'[Person Index] <= EARLIER ('Monthly Sales'[Person Index]))
This seems to work okay and I get the trendline below.
In the same table, I sorted Team, then Month and added another index (Team Index). Then I created the calculation below but it doesn't calculate correctly:
Team Cumulative Sales = CALCULATE (
SUM ( 'Monthly Sales'[Monthly Sales] ),
ALLEXCEPT ( 'Monthly Sales', 'Monthly Sales'[Team] ),
'Monthly Sales'[Team Index] <= EARLIER ('Monthly Sales'[Team Index]))
Note that in the resulting table below both the A5 and C3 calculations for Team Cumulative Sales are incorrect. I believe this is because there are multiple entries for those months for that team and it isn't handling it well. Any ideas?
If it helps, here's the merged table, showing how I merged the tables and created indexes. If there's a better way of doing it let me know
Thanks, in advance.
I came up with a solution to get around the Months that have multiple Sales records:
- Use the "Merge Queries as New" function to crate a new table from the Personal Sales. That way I don't mess with that table).
- Used the "Group By" function to combine the sales that are in the same month. After doing this I didn't have the problem with duplicates.
- Once I did that, using the statement from Phil (thanks, Phil!), I created a calculated column to create the cumulative sales, as appropriate.
I had to do this for each of the three charts I wanted (person, team and group). and it worked.
Here's what I ended up with.
4 Replies
I came up with a solution to get around the Months that have multiple Sales records:
- Use the "Merge Queries as New" function to crate a new table from the Personal Sales. That way I don't mess with that table).
- Used the "Group By" function to combine the sales that are in the same month. After doing this I didn't have the problem with duplicates.
- Once I did that, using the statement from Phil (thanks, Phil!), I created a calculated column to create the cumulative sales, as appropriate.
I had to do this for each of the three charts I wanted (person, team and group). and it worked.
Here's what I ended up with.
Glad to hear that you've solved this problem. You may help accept the solution above. Your contribution is highly appreciated.
HI mike1234
If you are doing this as a calculated column, then this might be closer to what you need
Team Cumulative Sales = CALCULATE ( SUM ( 'Monthly Sales'[Monthly Sales] ), FILTER(ALL( 'Monthly Sales' ), 'Monthly Sales'[Team] = EARLIER('Monthly Sales'[Team]) && 'Monthly Sales'[Sales Month] <= EARLIER ('Monthly Sales'[Sales Month])))Bare in mind, you probably want to use an actual date for your Month, rather than an integer to handle when you have data for more than a single year.
Thanks. I pasted the formula in (as a calculated column) and here's what I get.
It still looks like the Sales Months with multiple sales are not calculating correctly.
As a side note - For this use case I need to use Month as a relative month rather than actual month.
