Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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:
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 😞
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
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 :
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.
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |