Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi all,
Fairly new to DAX so apologies if I've missed something obvious. I am trying to create a measure that returns a distinct count of a managers employees who achieved or exceeded the NPS target (say +60). NPS Score is calculated using a simple measure:
NPS Score:=DIVIDE(SUM([Promoter])-SUM([Detractor]),SUM([NPS Survey Count]))*100
The raw data looks like this:
Date | Employee | Manager level 1 | Manager level 2 | NPS | Promoter | Detractor | NPS Survey Count |
1/1/23 | John Smith | Andrew Brown | Julie Jackson | 10 | 1 | 0 | 1 |
16/1/23 | John Smith | Andrew Brown | Julie Jackson | 4 | 0 | 1 | 1 |
I tried the below formula but it is counting each row where NPS was over target (any surveys scored 8+ on NPS). Where I would want it to count John Smith just once if his NPS score was 60 or over for the month (or whichever date context we're using).
UniqueAgentsAtTarget:=CALCULATE(DISTINCTCOUNT(NPS_Data[Employee]),filter(NPS_Data,[NPS Score]>=60))
Thanks for reading, if you need any more info let me know!
Solved! Go to Solution.
@Ash88 , try like
UniqueAgentsAtTarget:=Countrows(Filter(values(NPS_Data[Employee]),[NPS Score]>=60))
Try this:
UniqueAgentsAtTarget =
VAR Scores =
ADDCOLUMNS ( DISTINCT ( NPS_Data[Employee] ), "@Score", [NPS Score] )
RETURN
COUNTROWS ( FILTER ( Scores, [@Score] >= 60 ) )
Thanks for this! If I needed to change the target to a variable monthly target in future, do you know how I could implement that into this formula? IE target in a future month may increase from 60 to 65.
User | Count |
---|---|
84 | |
80 | |
69 | |
46 | |
46 |
User | Count |
---|---|
106 | |
50 | |
47 | |
40 | |
39 |