Forum Discussion

LeanneShapro's avatar
LeanneShapro
Icon for Advocate I rankAdvocate I
8 years ago

Calculate NPS Score with DAX

Hello Power BI Users -    I use the following excel formula to calculate my NPS score and I'd like to create the DAX equivalent. It should take the percentage of promoters (scores of 9 and 10) minu...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi LeanneShapro

You could create measures

Measure = CALCULATE(COUNT(Table1[name]),FILTER(ALL(Table1),[score]>8))

Measure 2 = CALCULATE(COUNT(Table1[name]),FILTER(ALL(Table1),[score]<7))

Measure 3 = CALCULATE(COUNT(Table1[name]),ALL(Table1))

Measure 4 = ([Measure]-[Measure 2])/[Measure 3]

Measure 5 = ROUNDUP([Measure 4],0)

Or a nested measure

Measure 6 =
VAR low =
    CALCULATE ( COUNT ( Table1[name] ), FILTER ( ALL ( Table1 ), [score] < 7 ) )
VAR high =
    CALCULATE ( COUNT ( Table1[name] ), FILTER ( ALL ( Table1 ), [score] > 8 ) )
VAR alname =
    CALCULATE ( COUNT ( Table1[name] ), ALL ( Table1 ) )
VAR percentage = ( high - low )
    / alname
RETURN
    ROUNDUP ( percentage, 0 )

 

Best Regards

Maggie