Budget Variance

[Anonymous]

Hello,

I am still fairly new to PBI.  I am creating visuals for Budget Variances.  I am given the Actuals and the Budget in the data set.  I created a measure to do so:

OL6 Variance = DIVIDE(SUM('Run Export Action'[Actual (USD)])-SUM('Run Export Action'[OL6 Budget (USD)]),SUM('Run Export Action'[OL6 Budget (USD)]),"N/A")

However, the problem I am having is when the Actuals are an amount but the budget is 0.  Any advice would be much appreciated, see below the highlighted section.

[gmsamborn]

Hi @Anonymous 

You didn't say what you would display instead of 0.

IF you need a flexible solution, maybe something like this would work.

OL6 Variance = 
VAR _Actual = SUM( 'Run Export Action'[Actual (USD)] )
VAR _Budget = SUM( 'Run Export Action'[OL6 Budget (USD)] )
VAR _Variance =
	DIVIDE(
		_Actual - _Budget,
		_Budget,
		0
	)
VAR _Logic =
	IF(
		NOT ISBLANK( _Actual ),
		IF(
			NOT ISBLANK( _Budget ),
			_Variance,
			"No Budget"
		),
		"No Actual"
	)
RETURN
	_Logic

Budget Variance.pbix

[Anonymous]

Hi this helps.  However, moreso if the budget is 0 and there is an amount in the actuals how would I get the outcome to show 100% instead of 0%?

[gmsamborn]

Hi @Anonymous 

In the above measure, change "No Budget" to 1.