Forum Discussion
Solved
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 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.
Phil_Seamark
Microsoft Employee
Microsoft EmployeeHI 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.