Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Column count

Good afternoon

First of all, I thank you for your attention.

I've been trying to find a solution for a very simple calculation to do with pivot tables, but I can't solve it in PBI.

In the following table I want to count the total sum of categories but only count it once per customer.

In the table above, I want to know how many categories the seller Pedro works by adding those of the two customers. That is, the total category count is very simple to calculate (16). The different count of categories (7) is also very second.

But if I want to calculate the total categories that work the two clients, 13, 7 of the client Murcia and 6 of the client Sevilla, I do not know how to calculate it. In short, what I want is that if a customer buys the same category on two different dates, I only count it once. If the two customers buy the same category, I must count it twice.

I hope I haven't messed up too much and thank you in advance for any help.

Greetings

  • Mikelytics's avatar
    Mikelytics
    3 years ago

    Hi  Syndicate_Admin 

     

    Please try the following measure:

     

     

    Count of Distinct Categories per Customer =

SUMX(
   Values(Table[Customer]),
   CALCULATE(
      DISTINCTCOUNT(Table[Category])
   )
)

     

     

    Best regards

    Michael

    -----------------------------------------------------

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

    @ me in replies or I'll lose your thread.

4 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    Hi  Syndicate_Admin 

     

    Please try the following measure:

     

     

    Count of Distinct Categories per Customer =

SUMX(
   Values(Table[Customer]),
   CALCULATE(
      DISTINCTCOUNT(Table[Category])
   )
)

     

     

    Best regards

    Michael

    -----------------------------------------------------

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

    @ me in replies or I'll lose your thread.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Good morning

      Thank you for yesterday's reply, but now I have another question.

      If on the same exercise you wanted to put a filter on the total units, what would it be like?

      That is, I would like to be able to count the total of categories but only when the total number of units of the same is greater for example to 36 units.

      Thanks in advance.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Syndicate_Admin ,

     

    The issue with this post has been solved, if there are other questions please publish a new post, there will be professional engineers, super users and forum users to help answer.

     

    Best Regards,

    Neeko Tang

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