Forum Discussion
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
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]) +0And 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
Community Support
Hi,
Please try to turn off the total in visual's Format->Total:
Best Regards,
Giotto Zhi
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
Super User
Please share the formula you are using
- 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
PaulDBrownCommunity Champion
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())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?ThanksLiam- PaulDBrown
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]) +0And 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?
