Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average Total

Hi,

 

I have a column which I have to display as Average values for the calculation to work.

 

This however, then displays the total as average which I cant have.

 

Is there any way I can remove the total for it? I need the 99 removed ideally.

 

 

Thanks in advance

 

Liam

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    6 years ago

    Anonymous 

     

    Make sure that the column you include in your ISINSCOPE measure is the same as the one you are using in your visual. (ie, if the rows are from a dim table, you must use that same column in  the ISINSCOPE measure)

     

    See this example:

    Rows for Item from Tabe A (see measure)

     

    The measures are:

     

     

    Average Actuals = AVERAGE('Table A'[Actuals]) +0

     

     

    And the one used in the "Average without totals" table (NOTE: the "Item" in rows is from Table A = same as measure):

     

     

    Average without totals = IF(ISINSCOPE('Table A'[Item]); [Average Actuals]; BLANK())

     

     

    If however I use a column from a different table as  rows, I get blank: ("Item" in rows from Table B)

     

    Does that solve the problem?

9 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try to turn off the total in visual's Format->Total:

     

    Best Regards,

    Giotto Zhi

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-gizhi-msft ,

       

      Apologies, I should have been clearer. 

       

      I have other columns in the table and this would remove all totals.

       

      Thanks

       

      Liam

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Seems fine, unless you want to ignore 0. What is the expected result and what you are getting

         

        AVERAGEX(vwtest,if(vwtest[Points]=0, blank(),vwtestPoints]))+0

         

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    Anonymous 

     

    Use the ISINSCOPE function.

    Say your table/matrix rows are from a column 'Table[id]', you can filter out the total with:

    Average without totals = IF(ISINSCOPE(Table[id]), [Your Average Measure], BLANK())
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi PaulDBrown ,

       

      Many thanks for your reply. Ive put it in but that measure displays nothing all the way down the column.

       

      I have 

      Average without totals = IF(ISINSCOPE(vwtest[Points]), AVERAGE(vwtest[Points])+0, BLANK()).
       
      Could you please confirm if that looks incorrect?
       
      Thanks
       
      Liam
      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        6 years ago

        Anonymous 

         

        Make sure that the column you include in your ISINSCOPE measure is the same as the one you are using in your visual. (ie, if the rows are from a dim table, you must use that same column in  the ISINSCOPE measure)

         

        See this example:

        Rows for Item from Tabe A (see measure)

         

        The measures are:

         

         

        Average Actuals = AVERAGE('Table A'[Actuals]) +0

         

         

        And the one used in the "Average without totals" table (NOTE: the "Item" in rows is from Table A = same as measure):

         

         

        Average without totals = IF(ISINSCOPE('Table A'[Item]); [Average Actuals]; BLANK())

         

         

        If however I use a column from a different table as  rows, I get blank: ("Item" in rows from Table B)

         

        Does that solve the problem?