Forum Discussion
Helper INPS last score per period
I am also getting same result with a bit different formula.
Is there anything wrong, your looking to fixed.
https://www.dropbox.com/s/9246ahe8ge1p3lo/NPS%20last%20score%281%29.pbix?dl=0
Community SupportYou could combine my measures below to one.
last date = CALCULATE(LASTDATE('calendar'[Date]),FILTER(Sheet3,NOT(ISBLANK(Sheet3[Score]))))
NumOfRespon =
CALCULATE (
DISTINCTCOUNT ( Sheet3[ID] ),
FILTER (
Sheet3,
NOT ( ISBLANK ( [Score] ) )
&& Sheet3[Response Date] = Sheet3[last date]
)
)
_promoters =
CALCULATE (
DISTINCTCOUNT ( Sheet3[ID] ),
FILTER (
Sheet3,
NOT ( ISBLANK ( [Score] ) )
&& Sheet3[Response Date] = Sheet3[last date]
&& [NPS label] = "Promoters"
)
)
_detractors =
CALCULATE (
DISTINCTCOUNT ( Sheet3[ID] ),
FILTER (
Sheet3,
NOT ( ISBLANK ( [Score] ) )
&& Sheet3[Response Date] = Sheet3[last date]
&& [NPS label] = "Detractors"
)
)
Final output = (([_promoters]/[NumOfRespon])-([_detractors]/[NumOfRespon]))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I
Thank you so much for your replay, unfortunately your solution has the same problem as mine.
I made a slight change to the data set in your PBIX dataset.
Now in August ID 21 has two scores , one passive and one promoter, and as in my calculation the NumOfResponses is showing the correct value ( one last score regardless of the label ( promoter , detractor , passive) where the measure for either Passive,Promoter,Detractor is showing the last score per label not the last score overall.
So in this example in August there is 24 distinct scores form the users ( two from ID 21 but NumOfResponses calculates this correctly as we want only the last one) but the other measures are adding to 25 which means that the ID 21 is counted twice , once as a promoter and once as a passive. So the filter context is similar to mine where label -> last date -> count .
What I am tying to do is last date values (in this example 24) and then the count of the labels from that (example .Promoter 9 , Detractor 6 , Passive 9 = 24 )
I am attaching your pbix file witch my changes
https://www.dropbox.com/s/17tyz6cilx930cj/NPS%20last%20score%20per%20period.pbix?dl=0
- amitchandak
Super User
Try Tried, but not able to match logic. Please see this screen shot. Can you explain by checking lines, maybe in excel,
What is considered as the promoter for Aug, Sep, and Q3. And final calculation
- Tazmastablasta
Hi amitchandak
I have simplified the example
Let's say in October we have this:
The condition is to only consider the last score per ID , so in this case it will 3 promoters.
Now both my mine and v-juanli-msft solutions are counting last Promoter,Detractor or Passive depending on a label,
which in the October example gives the total of 7 ( 3 Promoters ,2 passive , 2 Detractors) which is wrong
what I am trying to achieve is count of labels which where last scores.
In terms of the Quarter the result should be, 3 Detractors, 2 Passive, 3 Promoters.
Now in both examples ( mine and v-juanli-msft ) the distinct count of id with any label filters works well
NumOfRespon = SUMX(LASTDATE(VALUES('Calendar'[Date])), CALCULATE (DISTINCTCOUNT( 'NPS'[ID] ), FILTER ( 'NPS', 'NPS'[Score] <> BLANK () || 'NPS'[Score] = 0 ) ))and return the desired value but when we try to count the labels we have problems.
Here is the link another version of PBIX
https://www.dropbox.com/s/vbm6pndnengdgox/NPS%20last%20score.pbix?dl=0
- amitchandak
Please share expected values for the count of labels, response for Month and QTR