Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
asjones
Helper IV
Helper IV

Variance vs. Budget and Filters

I have a table of projects, expenses and budgets.  I wanted to calculated variance from budget then do some filtering on those that are say 10,000 under budget. I have run into a few problems

 

I used a new column of Variance = table1[actuals ytd] - table1[budget ytd]

 

However if there are multiple lines for a project I get the variance per line when I need it aggregated at the project level.

 

I get:

Proj1  Amount1  variance 1

Proj1 Amount2  variance 2

Proj1 Amount3  variance 3  

Proj2 Amount4 variance 4

Proj2 Amount5  variance 5

Proj2 Amount6  variance 6

Proj2 Amount7  variance 7

 

 I really just need Project 1 and Project 2 the variances aggregated.

 

Seems like it should be simple, but looks to be hard.

 

then after i would want to be able to filter on variance totals.

 

Any thoughts?

 

 

 Alan

 

1 ACCEPTED SOLUTION

No that should work.  If you put a chart/table in your report with Project Number as axis and the measure as the value, it will calculate that measure for each Project Number as if that were a filter applied to the underlying data.

 

In more complex examples, there may be performance differences between calculated DAX columns and columns created in the query editor.  The query editor generally will apply query folding and push the processing workload to the data source (where applicable), whereas the calculated DAX column happens within Power BI.  DAX is way more flexible than the query editor, however, so my best advice is to do the ETL and mashing phase as much as possible in the query editor, but save the advanced pivoting and calculating for DAX.

View solution in original post

5 REPLIES 5
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

If you just want a measure, you could use something like the following:

 

Proj1Variance = Calculate(Sum(TableName[Variance]),Filter(TableName, [Project] = "Proj1"))

dkay84_PowerBI
Microsoft Employee
Microsoft Employee

I would create the calculated variance column in the query editor, then use the "Group By" function to group rows by Project and sum the variance column.

thanks for the fast reply....

 

I also just changed the calculated column to a measure and it seemed to work.

 

YTD Variance = sum(CurYear[Actuals YTD]) - sum( CurYear[Budget YTD])

 

am i missing something as to why this might be bad?

 

thanks again

 

Alan

 

 

Hi @asjones,

The formula of Calculated Column and Calculated Measure in your scenario are same.

Did you select "Don't summarize" for actual & budget? it could be the reason to force data in table showing row by row

No that should work.  If you put a chart/table in your report with Project Number as axis and the measure as the value, it will calculate that measure for each Project Number as if that were a filter applied to the underlying data.

 

In more complex examples, there may be performance differences between calculated DAX columns and columns created in the query editor.  The query editor generally will apply query folding and push the processing workload to the data source (where applicable), whereas the calculated DAX column happens within Power BI.  DAX is way more flexible than the query editor, however, so my best advice is to do the ETL and mashing phase as much as possible in the query editor, but save the advanced pivoting and calculating for DAX.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.