topic Re: average of sumx calculation where results of measure > 0 in DAX Commands and Tips

average of sumx calculation where results of measure > 0

fisqkuz1 — Tue, 29 Mar 2022 08:51:10 GMT

Hi,

I am looking to get the average of a column which was calculated with SUMX. If get that it's not working because dax doesn't know the context, however I can't seem to figure this out.

Example:

test = SUMX(table1, table1[c1] - table1[2])

and then I want IF test > 0 -> take into account to average.

Re: average of sumx calculation where results of measure > 0

amitchandak — Tue, 29 Mar 2022 10:32:03 GMT

fisqkuz1 , if Test is a measure it needs a group by to check >0

Averagex(filter(Values(Table[Column]), [Test] >0), [Test])

Re: average of sumx calculation where results of measure > 0

v-jingzhang — Fri, 01 Apr 2022 09:59:03 GMT

Hi fisqkuz1

You can try this measure

AVG = AVERAGEX(FILTER('Table1','Table1'[C1]-'Table1'[C2]>0),'Table1'[C1]-'Table1'[C2])

Below is my sample data and measure result.

If this is not what you want, can you provide some sample data and expected result so that we can try to come up with a more accurate solution?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.