topic Re: Ignore average if not available in DAX Commands and Tips

Ignore average if not available

Anonymous — Fri, 05 Mar 2021 10:20:49 GMT

Hi Guys,

I wan't to ignore an average which is calculated in a certain metric when there is no data available for a certain year for another metric. I have this matrix:

Year	Avg1	Avg2
2017		15
2018	10	12
2019	14	19
2020	12	12
total	12	14.5

For my report, I'm calculating and presenting the overall difference between avg1 and avg2. What I wan't is when no data available for a certain year for avg1, don't calculate the average for avg2 (I'm not presenting the matrix, but only the overall). 15 is now influencing the overall comparison which is wrong. How to solve this?

The measures I'm using the calculate the averages are pretty basic: sum(sales) / distinctcount(orderid)

Re: Ignore average if not available

gvrajesh — Fri, 05 Mar 2021 11:11:25 GMT

Hi Anonymous

You can calculate Avg2 only when Avg1 is not blank using below logic

Avg2 = IF( ISBLANK(AVG1), BLANK(),sum(sales) / distinctcount(orderid))

Re: Ignore average if not available

rfigtree — Fri, 05 Mar 2021 11:18:41 GMT

i was thinking the same, but not too sure it will work when it calculates at the total level. as when the year context is removed [avg1] wont be blank so 2017 will be included? just thinking I could be wrong.

Re: Ignore average if not available

gvrajesh — Fri, 05 Mar 2021 12:18:33 GMT

Hi rfigtree

Yes you are correct. To fix that we've to create virtual table like below

Re: Ignore average if not available

rfigtree — Fri, 05 Mar 2021 12:24:12 GMT

cool, it is fun seeing peoples solutions on here. always learning something new.