How to show blank value in total

[Narukkp]

Hi Team,

I have a two metrics one is current year and second one last year. My formula of variance is either current year or last year is null then variance is blank else current year – last year. It is working as expected for row wise but total level it is not working as expected. How will I get the total as blank when if either current year or actual year is null.
Could you please let  me know how to write the dax formula for variance .
Note: If atleast one driver having current year and last year then we can not show blank in total and need to actual variance. 

[amitchandak]

@Narukkp , You can use isfiltered or isinscope or hasonevalue to get grand total row and display blank there

example

if( Isfiltered (Table[Date]), [Measure], blank())

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

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

[Narukkp]

Hi @amitchandak ,
Thanks for your reply, but if i am using IsInScope,hasonevalue then every time totals is showing  as blank but my case not every time if you observe my scenario i need the all driver variance total i.e if driver is having current year or previous year as null then we need to show the blank else we need to show the variance total.
so how will cover both scenarios at total level. 

[Narukkp · Accepted solution]

Hi All,
I got the solution using below code. Blank value handling in variance metric itself.

IF(HASONEVALUE(Supervisor_Driver_Mapping[Driver]),Fact_Measures[_OverallActual_Route Standard Hours_Variance],SUMX(VALUES(Supervisor_Driver_Mapping[Driver])

,[_OverallActual_Route Standard Hours_Variance]))