How to count based on another column

[ihungko]

Hi community folks,

I am building a KPI dashboard, but kind of struggling to write the correct measure. Can you please kindly help? Thanks a lot!

My dataset is like this, I want to calculate certain opportunity # based on the KPI criteria. The criteria is as follow:

• Calculate Won / Pending Opportunity #, for each opportunity, 1 credit for each unique KPI Product Group 
• For example,
  • Opportunity X, there are 2 products in two different KPI Product Group, so it will get 2 credits for Pending;
  • Opportunity Y, there are 3 products, but only in 2 unique KPI Product Group, so it will also get 2 credits for Pending;
  • Opportunity Z, there are 4 products all in KPI Product Group B, so it will only get 1 credit for Won

Opportunity Name	Product Name	Stage	KPI Product Group
Opportunity X	Product A1	Pending	A
Opportunity X	Product B1	Pending	B
Opportunity Y	Product A1	Pending	A
Opportunity Y	Product A2	Pending	A
Opportunity Y	Product B1	Pending	B
Opportunity Z	Product B1	Won	B
Opportunity Z	Product B2	Won	B
Opportunity Z	Product B3	Won	B
Opportunity Z	Product B4	Won	B

[Uzi2019]

Hi @ihungko 
You can simply write DISTINCTCOUNT().
If you want to take count against only opportunity name.

If this not what you are looking for then let me know the expected output like what other columns you are expecting and count value against it.

[ihungko]

Hi Uzi, thanks for your reply.

DISTINCTCOUNT was my first option, however, there is a sistuation when kpi product group has more than 2, for example like the table below:

The desired outcome would be 

Opportunity Name	Credit
Opportunity X	1
Opportunity Y	1
Opportunity Z	1
Opportunity AA	2
TOTAL	5

But if I use DISTINCTCOUNT on KPI Product Group, it will be 3 in total,

Opportunity Name	Product Name	Stage	KPI Product Group
Opportunity X	Product A	Pending	A
Opportunity Y	Product A	Pending	A
Opportunity Z	Product A	Pending	A
Opportunity AA	Product B	Pending	B
Opportunity AA	Product C	Pending	C

[ihungko]

@Uzi2019 

I created a column concatenating Opportunity Name and KPI Product Group, and then use Distinctcount. It worked! 

But I am still wondering if there's more efficient / lighter way to do so.