Ignore average if not available

[Anonymous]

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)

[gvrajesh]

Hi @Anonymous 

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

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

[rfigtree]

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.

[gvrajesh]

Hi @rfigtree 

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

[rfigtree]

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