Forum Discussion
Aggregation: How many users without revenue
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:
Ok yes you could be right depending on visual/page/report filters. Try this:
Total Revenue = SUM(Sales[Revenue)
then
Customers without revenue =
COUNTX (
VALUES ( Customer[CustomerKey] ),
FILTER ( Customer[CustomerKey], [Total Revenue] = 0 )
)Depending on the data type of CustomerKey, you may have to use COUNTAX(?). If this doesn't work then would be great to see the model and some sample data.
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:
