topic Re: Simple DAX Query Question in DAX Commands and Tips

Simple DAX Query Question

Josh0112 — Wed, 04 Oct 2023 00:49:53 GMT

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.

Re: Simple DAX Query Question

ChiragGarg2512 — Wed, 04 Oct 2023 03:29:27 GMT

Josh0112 , Explain the issue properly.

Re: Simple DAX Query Question

Josh0112 — Wed, 04 Oct 2023 04:20:35 GMT

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

Re: Simple DAX Query Question

vicky_ — Wed, 04 Oct 2023 04:27:49 GMT

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

Re: Simple DAX Query Question

Dangar332 — Wed, 04 Oct 2023 05:16:03 GMT

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.

Re: Simple DAX Query Question

Josh0112 — Wed, 04 Oct 2023 05:17:44 GMT

Thanks, this seemed to do the trick.

Re: Simple DAX Query Question

vicky_ — Thu, 05 Oct 2023 21:34:39 GMT

Nice! Glad to help