Reply
Frequent Visitor
Posts: 13
Registered: ‎03-08-2017
Accepted Solution

Aggregation: How many users without revenue

[ Edited ]

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?


Accepted Solutions
Frequent Visitor
Posts: 13
Registered: ‎03-08-2017

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

View solution in original post


All Replies
Member
Posts: 50
Registered: ‎02-14-2017

Re: Aggregation: How many users without revenue

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] )
Frequent Visitor
Posts: 13
Registered: ‎03-08-2017

Re: Aggregation: How many users without revenue

[ Edited ]

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!

Member
Posts: 50
Registered: ‎02-14-2017

Re: Aggregation: How many users without revenue

[ Edited ]

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....

Frequent Visitor
Posts: 13
Registered: ‎03-08-2017

Re: Aggregation: How many users without revenue

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

 

Member
Posts: 50
Registered: ‎02-14-2017

Re: Aggregation: How many users without revenue

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.

Frequent Visitor
Posts: 13
Registered: ‎03-08-2017

Re: Aggregation: How many users without revenue

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 

Frequent Visitor
Posts: 13
Registered: ‎03-08-2017

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

Frequent Visitor
Posts: 8
Registered: ‎02-25-2017

Re: Aggregation: How many users without revenue

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

Member
Posts: 50
Registered: ‎02-14-2017

Re: Aggregation: How many users without revenue


dexterz wrote:

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


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