Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 without = CALCULATE(
	DISTINCTCOUNT(Customer[CustomerKey]); FILTER(Sales;SUM(Sales[SalesRevenue]) = 0)
)

 

Which works fine as long as I'm showing all customers ID's in report view, but if I move to an aggregate level i.e. customer group, the function breaks.

 

Alternatively I was thinking:

Customers without revenue = CALCULATE(
	SUMX(Customer;
		DISTINCTCOUNT(Customer[CustomerKey]));FILTER(Sale;sum(Sales[SalesRevenue]) = 0)
)

So, how do I count all of our customers with 0 revenue,and get them shown on a aggregate level?

 

Edit:

Is it because I need to include an ALL(), so PBI ignores the filtered context in the viewer?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 Smiley LOL

View solution in original post

9 REPLIES 9
mattbrice
Solution Sage
Solution Sage

The first formula breaks at the aggregate level because in FILTER(Sales;SUM(Sales[SalesRevenue]) the SUM(Sales[SalesRevenue] part is summing up the revenue for all customers in the aggregation group, then comparing the number = 0.  If even one customer in the group has sales, the function will return blank.  

 

Your second formula is closer, but does the same thing.  Try this one:

 

Customers without revenue = SUMX ( Customer; [Customers without] )
Anonymous
Not applicable

Okay...

 

I've been at this all day, and I think I understand the logic of filter context, but can't seem to get it to work.

 

If I create a measure that's is the following:

Customers without revenue = CALCULATE(
	DISTINCTCOUNT(Customer[CustomerKey]);
		FILTER(Customer;
			SUMX(Sales;Sales[Revenue]) = 0)
)

I get an output counting all of my customers that has a revenue of 0, which is okay.

 

But the problem is, this doesn't work in a time dimension, as I'm filtering the customer table.

What is want is so filter my sales table to single out all customers with revenue = 0, and then do a distinct count of those.
Added to that, I want the count to work with my time dimension, so I can see the development over time.

Typically it's customers who have recieved a free sample, but then never made a purchase again, so I want them to count that distinct value.

 

I can get it to work on the individuel customer level with this measure:

Customers without revenue = SUMX(Customer; 
	CALCULATE(
		DISTINCTCOUNT(Sales[CustomerKey]);
		FILTER(Sales;
			SUMX(Sales;Sales[Revenue]) = 0)
	)
)

But if I remove the filter context of individuel customers, the formula no longer works.

I realise it's because I need to force the formula to calculate on a customer by customer level, but I simply can't wrap my head around how you should do that.

 

Help is really appreciated!

So the formula i provided above did not meet your requirements?  and when you say:

 

"But if I remove the filter context of individuel customers, the formula no longer works."  I don't undestand what you mean here.  Are you trying to do a visual without adding CustomerKey in row or column?  Just an aggregation group?  It is not clear.

 

Adding a time dimension, if done properly, should not matter.  The base formula should still work.  Can you provide a screenshot of the Matrix or other visual as you want the end reseult to look like?  I need to undestand the filter context under which you are placing the measure.  I am starting to think using SUMMARIZECOLUMNS is what you want....

Anonymous
Not applicable

I was thinking:

Customers without revenue = 
CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]);
FILTER(Sales;
SUMX(VALUES(Sales[CustomerKey]);Sales[Revenue]) = 0
))

I.e. I calculate the distinctcount of customerkey, which are filteres to only include customers with revenue = 0, and I'm ensuring it itterates over CustomerKey by using VALUES(Sales[CustomerKey].

 

If I understand it correctly, I don't need to include CustomerKey in my Column or row when I build a measure, (which is the goal).

From the matrix output I get from the above formula, the only problem seems to be that I get a distinct count as soon as a customer has had an order with a revenue of 0, and not the overall historical revenue = 0 

Anonymous
Not applicable

Hi again,

 

You're right, it's when I'm trying to construct a visual, without including the CustomerKey in the visual that the functionality breaks.

 

I want to be able to build a visual, were I don't have to include the "Customer" level in the hiearcy, for the function to work.

 

Here, in the first visual the functionality breaks, as PowerBI doesn't know on which granularity to calculate on, while on the second, were individuel customer ID's are included, you can see a count of 1, when revenues are equal to zero.

 

I've also been reading up on summarizecolumns, but been thinking I'm just missing a VALUES(), so i tell DAX to itterate on the CustomerID level, as VALUES is suppose to return a table of distinct values, which is kinda what I'm searching for, no?

 

Blabla.PNG

 

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.

Anonymous
Not applicable

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 Smiley LOL

COUNTROWS (
    EXCEPT ( VALUES ( Customer[CustomerKey] ), VALUES ( Sales[CustomerKey] ) )
)


@dexterz wrote:

COUNTROWS (
    EXCEPT ( VALUES ( Customer[CustomerKey] ), VALUES ( Sales[CustomerKey] ) )
)


I like this one better than mine.  Thanks for posting.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.