Using the Filter in DAX

[paulsmit]

Hi there,

I've been using basic formulae like the one shown below to give me results based on specific survey types. The example below brings back an average Value for Money score for Adults who stayed in tents and completed a survey.

AverageVFMAA = Calculate(average('1819  Data'[Value for money]),Filter('1819 Data','1819 Data'[Survey Type]="Adults Tent"))

What I would like to do is something very similar, but rather than working out the average VFM score I would like to work out the average Net Promoter Score.  My Net Promoter score is calculated using the following measure :-

 

Promoter =

CALCULATE ( COUNT ( '1819 TLF Data'[Row_Number] ), '1819 TLF Data'[Recommend] >= 9 )

Neutral =

CALCULATE (

COUNT ('1819 TLF Data'[Row_Number] ),

'1819 TLF Data'[Recommend] >= 7

&& '1819 TLF Data'[Recommend]< 9

)

Detractor =

CALCULATE ( COUNT ('1819 TLF Data'[Row_Number]), '1819 TLF Data'[Recommend] < 7 && '1819 TLF Data'[Recommend] >0)

NPS =

( [Promoter] - [Detractor] )

/CALCULATE ( COUNT ('1819 TLF Data'[Row_Number]), '1819 TLF Data'[Recommend] > 0 )*100

 I'm struggling to work out how I would show the Net Promoter Score for for Adults who stayed in tents?

Any help would be much appreciated

Cheers

Paul

[v-lili6-msft]

hi, @paulsmit 

Sample data and expected output would help tremendously. 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Lin