Forum Discussion
Aggregation: How many users without revenue
Hi, I'm having a problems with what seems like a fairly simple problem I can't get to work. I'm trying to figure out, how many of our customers had 0 revenue. Doing so with: Customers wi...
I finally cracked it!
First I already have the measure:
Sales Revenue = SUM(Sales[SalesRevenue])
Then to calculate customers without revenue I use:
Customers without revenue = CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]); FILTER(VALUES(Sales[CustomerKey]); Sales[Sales Revenue] = 0) )
The missing link was to NOT itterate over every single row within the FILTER(), but to just use the simple SUM from the first measure, so I actually get the total revenue for the unique customer, and not just a count if they ever had a line without revenue.
But the VALUES(Sales[CustomerKey]) was critical to tell PowerBI to itterate at that level.
This is me gradually learning DAX :smileylol:
