Column count

[Syndicate_Admin]

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

[Anonymous]

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. 

[Syndicate_Admin]

A million thanks for the solution and speed!!!

[Mikelytics · Accepted solution]

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]

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.