Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Budget Variance

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.

 

 
Need Help

3 Replies

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        gmsamborn
        Super User
        2 years ago

        Hi Anonymous 

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