cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Narukkp
Helper V
Helper V

How 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. 

 

Totalssue.JPG

1 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]))

View solution in original post

3 REPLIES 3
amitchandak
Super User
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/



New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !!

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. 

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]))

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors