Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Stacked Product Count

Hi Everyone,

I have been trying to figure this out but am getting nowhere. I have a dataset with customers and the products each customer is using. What I need is for get a count of what customer is using which product?

Ex.  Dataset has 5 Customers - Customer A is using products 1 and 2, Customer B is using products 2, 4, 5, Customer C is using products 1, 3, 4, 5, Customer D is using product 1, Customer E is using product 1, 2, 4, 5.

 

I want to be able to show how many customers are using one product, two products, 3 products, etc.

 

Any suggestion would be greatly appreciated. 

Need Help
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    Measure can't make slicers, Column can. You can try the following methods.

     

    Count Product = CALCULATE(DISTINCTCOUNT('Table'[Product]),ALLEXCEPT('Table','Table'[Customer]))

     

    Measure:

     

    Count Client = CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALLEXCEPT('Table','Table'[Count Product]))

     

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    1) product column insert into rows
    2) write down the measure as follows:

    Count= DISTINCTCOUNT('Таблица'[Клиенты])
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I am able to get the disticnt count of product, which is 5; however, what I need is of the 5 products, how many is each customer using?

    Customer A is using 3 of 5

    Customer B is using 2 of 5

    Customer C is using 1 of 5

    Customer D is using 4 of 5

     

    How do I get this measure?

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

     

    Count= DISTINCTCOUNT('table'[product])& "-"& calculate(DISTINCTCOUNT('table'[product]),allselect(client))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      After creating the measure, I was able to get the number of products each client is using!

       

      Is there a way to add a slicer for this? If I click 2, it'll show me how many clients are using 2 products? If I click 4, it'll show me how many clients are using 4 products?

      • v-zhangti's avatar
        v-zhangti
        Community Support
        3 years ago

        Hi, Anonymous 

         

        Measure can't make slicers, Column can. You can try the following methods.

         

        Count Product = CALCULATE(DISTINCTCOUNT('Table'[Product]),ALLEXCEPT('Table','Table'[Customer]))

         

        Measure:

         

        Count Client = CALCULATE(DISTINCTCOUNT('Table'[Customer]),ALLEXCEPT('Table','Table'[Count Product]))

         

        Is this the result you expect?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.