Forum Discussion
How to count based on another column
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 |
3 Replies
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
