Average Total

[Anonymous]

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

[PaulDBrown]

@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]

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 · Accepted solution]

@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?

[v-gizhi-msft]

Hi,

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

Best Regards,

Giotto Zhi

[Anonymous]

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]

Please share the formula you are using

[Anonymous]

Hi @amitchandak,

Its 

= AVERAGE(vwtest[Points])+0

[amitchandak]

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

[Anonymous]

Hi @amitchandak,

Its not the calculation thats the problem, its the displaying of the total (99) which is averaging all of the numbers which isnt needed, and I'm not sure whether theres a way around it.

Thanks in advance

Liam