How To Identify Grand Total Column - Similar to Hasonevalue - But Also When Only One Category

[Blinding1]

Hi guys,

I read that to identify the grand total column we can use Hasonevalue on the category field, however, this doesnt work if my categories are filtered to only one category. Any idea how to achieve that please?

To explain with an example, say I have the follwoing data table:

Name	Hours Worked	Week Ending
Scott	40	01-Jan-19
Ben	40	01-Jan-19
Gill	40	01-Jan-19
Scott	0	08-Jan-19
Ben	40	08-Jan-19
Gill	40	08-Jan-19
Scott	40	15-Jan-19
Ben	40	15-Jan-19
Gill	40	15-Jan-19

If I pivot this into a table I put names on rows and weekending on columns, and hours on values.

I then add the "weekending" category as a slicer to allow me to only show certain weekendings. BUT I always want the grand total to show the grand total (of all dates). Hasonevalue(Week Ending) works when there are more than one "weekendings" selected on the slicer, but it doesnt work when there is only one.

To be clear, I always want to clear the dates filter in the grand total column but it doesnt work when only one weekdning is selected.

Thanks guys

[v-chuncz-msft · Accepted solution]

@Blinding1 .

You may use ISINSCOPE instead.

[Blinding1]

Thanks for that formula - it might well do the trick. At the moment my working is in Excel PowerPivot which doesnt support this ISINSCOPE function, but I will try it in powerbi and see if it works. 

Kind of weird why it is availble in power bi  but not Excel (yet)

Thanks