Forum Discussion

Josh0112's avatar
Josh0112
New Member
2 years ago
Solved

Simple DAX Query Question

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. 

Exclusion rule in DAX
  • vicky_'s avatar
    vicky_
    2 years ago

    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])

     

6 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    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])

     

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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.

    • Josh0112's avatar
      Josh0112
      New Member
      2 years ago

      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