Best way to remove % change total from table

[lilych]

Hello -

What is the best way to remove the % Change total from the table? I tried to use HASONEVALUE() bt it doesn't seem to be working. 

Measure:

% Change = IF(NOT ISBLANK([# Cases]),DIVIDE([YoY Difference], [# Case LY]))

Thank you

[v-eachen-msft]

Hi @lilych ,

Try the following measure and check if it works.

% Change =
VAR a =
    IF ( NOT ( ISBLANK ( [# Cases] ) ), DIVIDE ( [YoY Difference], [# Case LY] ) )
RETURN
    IF ( ISINSCOPE ( 'Table'[Month] ), a, BLANK () )

[lilych]

Thanks @v-eachen-msft . When I try that measure, it returns no data - see %Change TEST below.

[amitchandak]

@lilych , isfiltered or isinscope should help

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Greg_Deckler]

@lilych - So you did HASONEVALUE on month column for example?

Measure:

% Change =

  VAR __Value = IF(NOT ISBLANK([# Cases]),DIVIDE([YoY Difference], [# Case LY]))

RETURN

  IF(HASONEVALUE([Month]),__Value,BLANK())

You can also count all the rows you in your table COUNTROWS(ALL('Table')) store it in a variable. Then do a COUNTROWS('Table'). If they match, you are in the Total line.

[lilych]

Thanks @Greg_Deckler . When I used the HASONEFILTER, it returns all blanks (see TEST % Change) which has the measure you outlined. Not sure why this is happening.

[Greg_Deckler]

@lilych - HASONEVALUE, not HASONEFILTER. Also could try ISINSCOPE, Also, could do:

Measure 

<blah blah blah>

VAR __CountAll = COUNTROWS(ALL('Table')

VAR __Count = COUNTROWS('Table')

RETURN

  IF(__Count = __CoutAll,BLANK(), <something else>)

[lilych]

Thanks @Greg_Deckler  - I meant HASONEVALUE, not HASONEFILTER, and it returned all blanks. I've tried ISINSCOPE can't seem to figure that out either. I'm new to DAX and will continue try to find out how to make this work. 

Could it be that it is still calculating the % CHange of the grand total, which is why it isn't working?

Thanks for the help