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.
Dear PowerBI experts,
after days spent on this forum and visited hundreds of other web resources I've come to the conclusion that I'm no longer able to sort out my issue by myself, hence badly need your support in my project. The case is: have to somehow build a measure that counts distinct # of Customers from YTD perpective with an additional condition that only those Cusotmers should be counted with revenue different than 0 in the same period of time (YTD as well).
So far I've made two steps so far which works like a charm:
Step1 = CALCULATE ( DISTINCTCOUNT ( FactTM1[Customer] ) ) - this one calculates all distinct Customers from the database
Step2 = CALCULATE ( [Step1], ALL ( DimCalendar ), DATESYTD ( DimCalendar[Date] ) ) - this one add YTD context to the Step1
Step3 = ??? - missing part, I have no clue how to add a condition here with sth like that: "calculate pool of customers from Step2 with a prerequisite of YTD revenue different than 0". Of course I've got a separate measure for YTD revenue:
YTD_USD = CALCULATE (SUM(FactTM1[Revenue]), DATESYTD ( DimCalendar[Date] ), ALL ( DimCalendar ))
so initially I thought of sth like this for Step3: = CALCULATE([Step2], YTD_USD<>0) but the PowerBI displays: "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Got lost & stucked there, so desperately looking for any form of help here. Anyone? 🙂
Solved! Go to Solution.
FYI that you don't need the CALCULATE around your Step 1 measure. Please try this expression to get your desired result.
Customers with Sales YTD =
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( FactTM1[Customer] ),
CALCULATE (
SUM ( FactTM1[Revenue] )
) > 0
)
),
ALL ( DimCalendar ),
DATESYTD ( DimCalendar[Date] )
)
You may not need the ALL in there, so you can try w/o that too.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi,
Try these measures
Revenue = sum(data[rev])
YTD revenue = calculate([revenue],datesytd(DimCalendar[Date],"31/12"))
Measure = countrows(filter(values(data[Customer code]),[YTD revenue]>0))
To your visual ensure that you drag Month/Year from the calendar visual.
Hope this helps.
Hi Ashish,
unfortunately your solution does not display correct values but still thank you for involvement here, appreciated! Glad that Pat's solution works!:)
FYI that you don't need the CALCULATE around your Step 1 measure. Please try this expression to get your desired result.
Customers with Sales YTD =
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( FactTM1[Customer] ),
CALCULATE (
SUM ( FactTM1[Revenue] )
) > 0
)
),
ALL ( DimCalendar ),
DATESYTD ( DimCalendar[Date] )
)
You may not need the ALL in there, so you can try w/o that too.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat,
jeeeeez, couldn't figure this out by myself so thanks a million, truly appreciate it! Works like a charm now! 🙂
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |