topic Re: Need a Count of Customers that Own a Certain Number of Products in DAX Commands and Tips

Need a Count of Customers that Own a Certain Number of Products

Blaggy — Mon, 24 Jul 2023 06:09:18 GMT

I've been struggling with this, searching online, and I cannot seem to overcome this hurdle.

I'm taking a list of customers that have Revenue in one or more of four products. I have written the below DAX to create a measure for how many customers own each of the Products:

Product 1 Cust Count = CALCULATE(

COUNT(Table[Customer]),

Table[Product] = "Product 1"

)

Product 2 Cust Count = CALCULATE(

COUNT(Table[Customer]),

Table[Product] = "Product 2"

)

and so on. Then, I made a measure to show me the # of Products each customer has, called "Count of Products":

Count of Products = [Product 1 Cust Count]+[Product 2 Cust Count]+[Product 3 Cust Count]+[Product 4 Cust Count]

This works good when I add customers to a matrix by quarter, I can see the # of products each owns.

But, I cannot get over this last step, which seems like it should be simple. I just want to create a measure that shows the number of customers that only own 1 product, the number of customers that own 2 products, etc. so that I can see the trends over time of how many customers own 1 product vs. 2 products vs. 3 products, etc. I've tried this several ways, yet I don't get any results. I've tried variations around something that looks like this (using customers that own 2 products as an example):

2 Product Customers = CALCULATE(

COUNT(Table[Customer]),

FILTER(Table,Table[Count of Products]=2)

)

If I try to add this measure to a new visual, it just gives me blanks (no data), even though the DAX expression is not giving me any errors.

Can someone suggest a better way of going about this (or where I'm going wrong above)? I have a feeling I'm making this way harder than it should be.

Thanks!

Re: Need a Count of Customers that Own a Certain Number of Products

tamerj1 — Mon, 24 Jul 2023 06:42:14 GMT

Hi Blaggy
Please refer to attached sample file with the proposed solution

Product Count Parameter = SELECTCOLUMNS ( GENERATESERIES ( MINX ( VALUES ( Sales[CustomerKey] ), CALCULATE ( DISTINCTCOUNT ( Sales[ProductKey] ) ) ), MAXX ( VALUES ( Sales[CustomerKey] ), CALCULATE ( DISTINCTCOUNT ( Sales[ProductKey] ) ) ), 1 ), "Number of Products", [Value] )

Number of Customers = COUNTROWS ( FILTER ( VALUES ( Sales[CustomerKey] ), CALCULATE ( DISTINCTCOUNT ( 'Sales'[ProductKey] ) ) = SELECTEDVALUE ( 'Product Count Parameter'[Number of Products] ) ) )

Re: Need a Count of Customers that Own a Certain Number of Products

Blaggy — Mon, 24 Jul 2023 20:59:19 GMT

Thank you for this - gets me super close. This is resulting in counts being slightly too high due to the fact that I want the product count to ignore any situations where the product is resulting in net negative sales (credit) - i.e. the Sales (or I guess "Net Price" using your example data) would need to be > 0. Been trying to figure out where to weave that into your DAX, but I'm not good at DAX when it gets to this level!

I really appreciate the quick response!

Re: Need a Count of Customers that Own a Certain Number of Products

Blaggy — Tue, 25 Jul 2023 17:36:56 GMT

Hi tamerj1 , thank you again for your help. I've been trying multiple different ways to filter-out any customers where revenue is not >0. I'm sure it's very simple, but for the life of me, I cannot figure it out. I'm horrible at DAX! Could you let me know if you have a simple addition to your DAX that will achieve this? Thanks!

Re: Need a Count of Customers that Own a Certain Number of Products

tamerj1 — Wed, 26 Jul 2023 06:44:53 GMT

Hi Blaggy
If you have the Revenue measure, you can use it in the filter as follows

Number of Customers = COUNTROWS ( FILTER ( VALUES ( Sales[CustomerKey] ), CALCULATE ( DISTINCTCOUNT ( 'Sales'[ProductKey] ) ) = SELECTEDVALUE ( 'Product Count Parameter'[Number of Products] ) && [Revenue] > 0 ) )

Re: Need a Count of Customers that Own a Certain Number of Products

Blaggy — Wed, 26 Jul 2023 17:54:09 GMT

Thank you tamerj1 . Gets me closer, but not 100% correct. What this is doing is pulling all of the customers with revenue <0 out of the first category. For example, in the prior quarter, in my dataset, there were 3 customers with Revenue <0, but one of those was in the [Number of Products] = 1 category, one of those was in the [Number of Products] = 2 category, and the other was in the [Number of Products] = 3 category. However, adding the "&& [Revenue] > 0" in that DAX takes all of those customers out of the [Number of Products] = 1 category, which isn't correct. Is there a different way to write this so that it ensures that it gets to the correct [Number of Products]?

Thanks again - getting SUPER close!

Re: Need a Count of Customers that Own a Certain Number of Products

Blaggy — Wed, 26 Jul 2023 20:41:00 GMT

Disregard this last msg from me, tamerj1 . I made a couple of tweaks and got it to work! Thanks for all of your help - would have never gotten there on my own!