Hide row in visualization but include in grand total

[Anonymous]

I have a table of regions and net sales. There is a row that has a blank value for a region, but still has net sales. I would like to hide this row from view, but still include all the data in that row in the grand total. Is this possible?

I've tried using a page-level or visualizaton-level filter, but while the row dissappears it also removes the data from the grand total. 

Thank you in advance.

[Anonymous]

Hi @Anonymous

You can do this. You can filter the blank region ( For ex: In visual filter)

Create this measure.

Grand Total = IF(COUNTROWS('Grand Total')<>COUNTROWS(ALLSELECTED('Grand Total')),

SUM('Grand Total'[Sales]),

CALCULATE(SUM('Grand Total'[Sales]),ALL('Grand Total')))

Thanks
Raj

[Anonymous]

Hi Raj,

Thanks for the reply, I have some follow up questions.

If my table's name is Net Sales, and the name of the column being summed is also Net Sales, 

Grand Total = IF(COUNTROWS('Net Sales')<>COUNTROWS(ALLSELECTED('Net Sales')),

SUM('Net Sales'[Net Sales]),

CALCULATE(SUM('Net Sales'[Net Sales]),ALL('Net Sales')))

Would this be correct?

Also, how would this measure be implemented into the matrix? I have created the measure but it is not being used.

Thank you.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a measure as below.

Grand Total = IF(MAX('net sales'[Region]) = BLANK(),BLANK(),CALCULATE(SUM('net sales'[net sales])))

Also please find the pbix attached.

Regards,

Frank

[kdhuri]

it causes performance issue in direct query mode. Is there any way this can be optimized. Also with this any other measure you bring in matrix needs to be manipulated like above.

I applised physical filter and used CALCULATE(Expression, ALL(Ignore Filter column) and it worked. but again lot of performance issue with direct query mode.

[v-frfei-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank