Forum Discussion
wmcclure
Helper I
Helper INPS Score needs additional filter
Hello all! I have a DAX formula in place for calculating NPS score based on information that customers provide. I need to add some logic to make sure that NPS is only calculated once per customer...
Hi wmcclure ,
I apologize for not responding to your message until now.The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a measureNPS Score = VAR ResponseCount = COUNTROWS(SUMMARIZE('Table','Table'[uuid])) VAR PromoterCount = COUNTROWS(FILTER(SUMMARIZE('Table',[uuid],"Score",MAX('Table'[NPS Score (Our Company Score)])),[Score] >=9)) VAR DetractorCount = COUNTROWS(FILTER(SUMMARIZE('Table',[uuid],"Score",MAX('Table'[NPS Score (Our Company Score)])),[Score] <= 6)) VAR Score = IF( ResponseCount >0 ,(0 + (PromoterCount - DetractorCount) / ResponseCount * 100),BLANK()) RETURN IF(NOT(ISBLANK(ResponseCount)), Score)2.Final output
wmcclureHelper I
Helper IThis is a quick version of the data with the expected results included
| uuid | Deal Number | Deal Domain | Customer | Manufacturer | CSAT Score (Manufacturer Score) | NPS Score (Our Company Score) | Prmotor or detractor? |
| 106 | 124500 | Public Cloud | Redacted | Redacted | 5 | 10 | p |
| 104 | 133934 | Digital Workspace | Redacted | Redacted | 3 | 10 | p |
| 103 | 134539 | Data Center | Redacted | Redacted | 3 | 6 | d |
| 103 | 137171 | Data Center | Redacted | Redacted | 3 | 6 | DUPLICATE |
| 45 | 133190 | Data Center | Redacted | Redacted | 3 | 10 | p |
| 44 | 133745 | Data Center | Redacted | Redacted | 4 | 8 | |
| 43 | 129736 | Digital Workspace | Redacted | Redacted | 3 | 10 | p |
| 43 | 132535 | Digital Workspace | Redacted | Redacted | 3 | 10 | DUPLICATE |
| 42 | 99732 | Public Cloud | Redacted | Redacted | 5 | 10 | p |
| 42 | 141020 | Enterprise Networking | Redacted | Redacted | 5 | 10 | DUPLICATE |
| 41 | 119798 | Public Cloud | Redacted | Redacted | 4 | 10 | p |
| 40 | 132026 | Digital Workspace | Redacted | Redacted | 5 | 10 | p |
| Response Count | 9 | ||||||
| Promotor Count | 7 | ||||||
| Detractor Count | 1 | ||||||
| Expected NPS Score | 0.67 |
Hi wmcclure ,
I apologize for not responding to your message until now.The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a measure
NPS Score =
VAR ResponseCount = COUNTROWS(SUMMARIZE('Table','Table'[uuid]))
VAR PromoterCount = COUNTROWS(FILTER(SUMMARIZE('Table',[uuid],"Score",MAX('Table'[NPS Score (Our Company Score)])),[Score] >=9))
VAR DetractorCount = COUNTROWS(FILTER(SUMMARIZE('Table',[uuid],"Score",MAX('Table'[NPS Score (Our Company Score)])),[Score] <= 6))
VAR Score = IF( ResponseCount >0 ,(0 + (PromoterCount - DetractorCount) / ResponseCount * 100),BLANK())
RETURN IF(NOT(ISBLANK(ResponseCount)), Score)2.Final output