Forum Discussion

yakovlol's avatar
yakovlol
Icon for Resolver I rankResolver I
3 years ago
Solved

Calculate Average without Zero values based on measure

Hello Team,
Could you please help me with creating a measure?
I need to Calculate the Average for (No group) ignoring zeros. The values for the second column are based on another measure [Count Month]. So I need to ignore zeros but to leave them in the matrix. 

Average CountMonths = 
AVERAGEX(VALUES(Table1[id]),[Count Months])

My matrix with the current calculation

 

Desired output to have 2 for "No group", but 0 should stay where they are

Many thanks for you help!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi yakovlol ,

     

    I suggest you to create a new [Average CountMonths] table based on original one.

    New Average CountMonths = 
AVERAGEX(FILTER(VALUES(Table1[id]),[Average CountMonths]<>0),[Average CountMonths])+0

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable Member
    3 years ago
    Maybe you can add a filter:
     
    Average CountMonths =
    AVERAGEX('Table1',CALCULATE([Count Months],FILTER('Table1',[Count Months]<>0)))
    • yakovlol's avatar
      yakovlol
      Icon for Resolver I rankResolver I
      3 years ago

      Hello mlsx4 

      Many thanks for your help, but when I'm using this measure I have 1.00 everywhere, actually don't know why.
      But for id 5 and 7 I need to have 3.00 like in picture above

      Average CountMonths =
      AVERAGEX('Table1',CALCULATE([Count Months],FILTER('Table1',[Count Months]<>0)))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi yakovlol ,

         

        I suggest you to create a new [Average CountMonths] table based on original one.

        New Average CountMonths = 
AVERAGEX(FILTER(VALUES(Table1[id]),[Average CountMonths]<>0),[Average CountMonths])+0

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.