Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aavilap
Helper I
Helper I

Cummulative Total of Measure

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:

 

MonthReached CustomersCumm Reached CustomersDESIRED Result
01-Jan-17222222
01-Feb-17233456
01-Mar-173247103
01-Apr-172751154
01-May-173255209
01-Jun-173059268

 

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 

1 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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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: 

MonthReached CustomersCumm Reached CustomersUsing SUMXDESIRED Result
01-Jan-17222222322
01-Feb-17233431556
01-Mar-173247376103
01-Apr-172751415154
01-May-173255523209
01-Jun-173059615268

 

It is adding something but it is not making sense 😞

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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!!

Is this what you are looking for :

 

customer cummulative.PNG



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k Yes that would be it!

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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks!

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:

 

MonthReached CustomersCumm Reached CustomersDESIRED Result
01-Jan-17222222
01-Feb-17233456
01-Mar-173247103
01-Apr-172751154
01-May-173255209
01-Jun-173059268

 

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 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.