Cummulative Total of Measure

[aavilap]

Hello! 

Hope someone can help me with this I have been trying to create a measure that adds the monthly result of another calculated measure which uses COUNTROWS but I'm not being able to get the desired result, my measures are defined as follows:

Reached Customers = COUNTROWS(FILTER(VALUES('Monthly'[Customers]),AND('Monthly'[PY Salesl]>1,[TY Sales]>1)))

Cumm Reached Customers =

VAR EarliestDate = CALCULATE(MIN(Calendar[Month]),ALLSELECTED())
RETURN CALCULATE([Reached Customers],FILTER(ALL(CCalendar[Month]),Calendar[Month]<= MAX(Calendar[Month]) && Calendar[Month]>= EarliestDate))

With this I get the following result:

Month	Reached Customers	Cumm Reached Customers	DESIRED Result
01-Jan-17	22	22	22
01-Feb-17	23	34	56
01-Mar-17	32	47	103
01-Apr-17	27	51	154
01-May-17	32	55	209
01-Jun-17	30	59	268

As you see the Cumm Reached measure is only adding the different customers from each month, however what I need is for it to add month by month result (as shown in column DESIRED Result), I understand this is not working because my original measure "New Customers" is using COUNTROWS and VALUES thus filtering only distinct values, which is actually what I need for that measure, the problem comes when trying to add those values. 

Please help! 

Thanks!

Ale 

[parry2k]

I believe you need to use SUMX to calculate cummulative count of your customers

[aavilap]

@parry2k I've tried using SUMX in my formula :

Clientes Toc_ACC = VAR EarliestD = CALCULATE(MIN(Calendario[Mes]),ALLSELECTED())
RETURN CALCULATE(SUMX('Monthly SellOut',[Clientes Tocados]),FILTER(ALL(Calendario[Mes]),Calendario[Mes]<= MAX(Calendario[Mes]) && Calendario[Mes]>= EarliestD)) 

But I get some really weird results: 

Month	Reached Customers	Cumm Reached Customers	Using SUMX	DESIRED Result
01-Jan-17	22	22	223	22
01-Feb-17	23	34	315	56
01-Mar-17	32	47	376	103
01-Apr-17	27	51	415	154
01-May-17	32	55	523	209
01-Jun-17	30	59	615	268

It is adding something but it is not making sense 😞

[Greg_Deckler]

Check out my article on Groups and Super Groups as this has a very similar issue to solve. Generally, use SUMMARIZE to generate a table with your measure and then use an X aggregator like SUMX, AVERAGEX, etc. to return the value you are seeking:

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[parry2k]

Are you ok to share sample data in Excel file and share it thru google drive/onedrive and will gt back to you with solution.

[aavilap]

@parry2k Sure, I've created a sample PBIX showing the issue, in the following link: PBIX & Data Sample 

It's a one drive link, this will not show the numbers I had in the example because I was using my real data, would share it but it has a lot of measures and they are not named as in the example, I'm afraid it would be easy to get lost :s

Please let me know if you find a way ! thanks!!

[parry2k]

Is this what you are looking for :

[aavilap]

@parry2k Yes that would be it!

[parry2k · Accepted solution]

Create two measures:

Distinct Customers = DISTINCTCOUNT('Sample'[Customer])

Cummulative Customers = 
SUMX(
FILTER(ALL('Sample'[Date]), 'Sample'[Date] <= MAX('Sample'[Date])),
[Distinct Customers]
) 

Drop date, and these two measures in a table and you have it.

[aavilap]

Thanks!

[aavilap]

Hello! 

Hope someone can help me with this I have been trying to create a measure that adds the monthly result of another calculated measure which uses COUNTROWS but I'm not being able to get the desired result, my measures are defined as follows:

Reached Customers = COUNTROWS(FILTER(VALUES('Monthly'[Customers]),AND('Monthly'[PY Salesl]>1,[TY Sales]>1)))

Cumm Reached Customers =

VAR EarliestDate = CALCULATE(MIN(Calendar[Month]),ALLSELECTED())
RETURN CALCULATE([Reached Customers],FILTER(ALL(CCalendar[Month]),Calendar[Month]<= MAX(Calendar[Month]) && Calendar[Month]>= EarliestDate))

With this I get the following result:

Month	Reached Customers	Cumm Reached Customers	DESIRED Result
01-Jan-17	22	22	22
01-Feb-17	23	34	56
01-Mar-17	32	47	103
01-Apr-17	27	51	154
01-May-17	32	55	209
01-Jun-17	30	59	268

As you see the Cumm Reached measure is only adding the different customers from each month, however what I need is for it to add month by month result (as shown in column DESIRED Result), I understand this is not working because my original measure "New Customers" is using COUNTROWS and VALUES thus filtering only distinct values, which is actually what I need for that measure, the problem comes when trying to add those values. 

Please help! 

Thanks!

Ale