Prevent Blank Rows From Showing

[SimonNU]

The below variance formula calculates correctly except it lists all dimensions in a pivot table, even ones which calculate to nothing. So when pivoted against a large dimension column, the pivot table blows to 1000 lines for example, instead of the 5 which actually contain data.

How do I prevent this from happening?

Metric Variance:=IF(HASONEVALUE('Source'[Data]),
   SWITCH(VALUES('Source'[Data]),
     "Current Year Actuals", 1,
     "Last Year Actuals", DIVIDE(CALCULATE([Metric],'Source'[Data]="Current Year Actuals") - CALCULATE([Metric],'Source'[Data]="Last Year Actuals"),CALCULATE([Metric],'Source'[Data]="Last Year Actuals")),
     "Current Year Plan", DIVIDE(CALCULATE([Metric],'Source'[Data]="Current Year Actuals") - CALCULATE([Metric],'Source'[Data]="Current Year Plan"),CALCULATE([Metric],'Source'[Data]="Current Year Plan")),
     "Current Year Forecast", DIVIDE(CALCULATE([Metric],'Source'[Data]="Current Year Actuals") - CALCULATE([Metric],'Source'[Data]="Current Year Forecast"),CALCULATE([Metric],'Source'[Data]="Current Year Forecast")),
     BLANK()
    )
  )

 

[SimonNU]

Please disregard. I simply needed to account for all results using BLANK()

Metric Variance:=IF(HASONEVALUE('Source'[Data]),   SWITCH(VALUES('Source'[Data]),
     "Current Year Actuals", BLANK(),
     "Last Year Actuals", DIVIDE(CALCULATE([Metric],'Source'[Data]="Current Year Actuals") - CALCULATE([Metric],'Source'[Data]="Last Year Actuals"),CALCULATE([Metric],'Source'[Data]="Last Year Actuals")),
     "Current Year Plan", DIVIDE(CALCULATE([Metric],'Source'[Data]="Current Year Actuals") - CALCULATE([Metric],'Source'[Data]="Current Year Plan"),CALCULATE([Metric],'Source'[Data]="Current Year Plan")),
     "Current Year Forecast", DIVIDE(CALCULATE([Metric],'Source'[Data]="Current Year Actuals") - CALCULATE([Metric],'Source'[Data]="Current Year Forecast"),CALCULATE([Metric],'Source'[Data]="Current Year Forecast")),
     BLANK()
    ),
BLANK()
  )