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

[Blaggy]

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!

[Blaggy]

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!

[Blaggy]

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!

[Blaggy]

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!

[Blaggy]

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!

[tamerj1 · Accepted solution]

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

[tamerj1]

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] ) 
    )
)