Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Ignore average if not available

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:

YearAvg1Avg2
2017 15
20181012
20191419
20201212
total1214.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)

4 Replies

  • gvrajesh's avatar
    gvrajesh
    Frequent Visitor
    5 years ago

    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's avatar
      rfigtree
      Icon for Resolver III rankResolver III
      5 years ago

      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's avatar
        gvrajesh
        Frequent Visitor
        5 years ago

        Hi rfigtree 

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