Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |