Forum Discussion
Helper VHow to show blank value in total
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.
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]))
3 Replies
- amitchandak
Super User
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/
NarukkpHi 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
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]))
