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,
I have total sales measure which is sum of sales for the selected period. Then I'm calculating another measure on top of it to identify products with non zero sales. Even when total sales is zero for productid 4 and 5 my Non Zero Product Sales is considering them. I need to only see productid 1 when June 2019 is selected. Why is the second measure true when 0 <> 0. Appreciate the help.
excel - https://www.dropbox.com/s/za0bko4gqtg8oa8/zero%20not%20equal.xlsx?dl=0
pbix - https://www.dropbox.com/s/ue4f54us94s2ie3/Zero%20not%20equal.pbix?dl=0
Solved! Go to Solution.
Hi,
Try these measures
Total Sales = SUM(Sheet1[sales])
Non Zero Product Sales = CALCULATE(DISTINCTCOUNT(Sheet1[productid]), FILTER(VALUES(Sheet1[productid])),[Total Sales] <>0))
Hope this helps.
Hi,
Try these measures
Total Sales = SUM(Sheet1[sales])
Non Zero Product Sales = CALCULATE(DISTINCTCOUNT(Sheet1[productid]), FILTER(VALUES(Sheet1[productid])),[Total Sales] <>0))
Hope this helps.
@Anonymous
You have a basic logic/concept mistake.
You need to sum the sales by ID.
Something like
Column Non Zero Product Sales = VAR TOTAL_SALES_PER_PRODUCTID = CALCULATE ( SUM ( Sheet1[sales] ), FILTER ( Sheet1, Sheet1[productid] = EARLIER ( Sheet1[productid] ) ) ) RETURN IF ( TOTAL_SALES_PER_PRODUCTID = 0, 0, TOTAL_SALES_PER_PRODUCTID )
And then work according to the results in that column.
It will give you 0 for ID 4,5.
Then Count
Non Zero Product Sales = CALCULATE ( DISTINCTCOUNT ( Sheet1[productid] ), FILTER ( Sheet1, Sheet1[Column Non Zero Product Sales] <> 0 ) )
Thanks!
A
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 |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |