Incorrect cumulative values when creating multiple line charts with multiple cumulative values

[mike1234]

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.

[mike1234 · Accepted solution]

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.

Sample PBIX

[v-chuncz-msft]

@mike1234,

Glad to hear that you've solved this problem. You may help accept the solution above. Your contribution is highly appreciated.

[Phil_Seamark]

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.

[mike1234]

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.