Simple DAX Query Question

[Josh0112]

I'm trying to create the following measure. I have two columns, one is a score and one is a text response. Like below

NPS     Theme

100        good

0            good

-100       bad

100        other

100        bad

100        fun

I want to be able to create a table that can show what the average of the NPS column would be if we didn't include each of the specific text themes. Something like below. Example for good theme would be (-100+100+100+100)/4

Theme   Average of NPS when theme not included

good        50   

bad          75

other        40

fun           40

Just not sure where to start with this one. Any help would be greatly appreciated. 

[Dangar332]

Hi, @Josh0112 

 make new table and use below code

new table = SUMMARIZECOLUMNS(yourtable[theme],

                              "s",CALCULATE(AVERAGE(yourtable[nps]),

                                          yourtable[theme]<>SELECTEDVALUE(yourtable[theme])

                                          )

                            )

here s = Average of NPS when theme not included

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[vicky_ · Accepted solution]

This DAX measure worked for me:

Average of NPS when theme not included =
var themeNotIncluded = FILTER(ALL('Table'), 'Table'[Theme] <> SELECTEDVALUE('Table'[Theme]))
return AVERAGEX(themeNotIncluded, 'Table'[NPS])

[Josh0112]

Thanks, this seemed to do the trick. 

[vicky_]

Nice! Glad to help

[ChiragGarg2512]

@Josh0112 , Explain the issue properly.

[Josh0112]

Can you let me know what additional information i could include to sufficiently explain?

In short i'm trying to create a measure which is AVERAGE(NPS) but to somehow use the discrete text combinations in theme column (good, bad, other, fun) as an exclusion filter. Each row has a unique id assigned to it so can be treated as a single response in a survey. E.g. first person gave a score of '100' and a theme of 'good' , second person gave a score of '0' and a theme of 'good' etc. 

So when i drop this in to a table against the theme 'good' in the theme column i want it to show the average of NPS for all themes combined except for the nps scores associated to the responses that answered 'good'. In the example i provided this would mean that the average of all NPS scores excluding the ones with text response 'good' = -100 (bad) + 100 (other) + 100 (bad) + 100 (fun) / 4 = 50