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 remov...
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?

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?