cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## data in excel, budget vs actual = variance formula

Hello all,

i have the below data in excel

and then i have a powerbi table

i would like to have another column with the variance per month.

any idea?

thank you

1 ACCEPTED SOLUTION
Memorable Member

Hello you probably want to make three measures (if you havent already made the first 2)

1. Actual=SUM(Table1[Actual])

2. Budget=SUM(Table1[Budget])

3. Variance=[Actual]-[Budget]

Drag them all into your table

// if this is a solution please mark as such. Kudos always appreciated.
4 REPLIES 4
Memorable Member

Hello, you need to have the three measures i mentioned earlier, each dragged in. I think you have just a single measure there with the actual/budget as a column header.

// if this is a solution please mark as such. Kudos always appreciated.
Memorable Member

Hello you probably want to make three measures (if you havent already made the first 2)

1. Actual=SUM(Table1[Actual])

2. Budget=SUM(Table1[Budget])

3. Variance=[Actual]-[Budget]

Drag them all into your table

// if this is a solution please mark as such. Kudos always appreciated.
New Member

thank you @samdthompson  that worked great but ihave a follow up.

i have a matrix and if i add the measure "Variance" at that matrix it appears at both columns like below

how can i remove the column marked with blue?

thanks in advance

Super User

Hi,

These measures should work

Budget total = calculate(sum(Data[amount]),data[type]="budget")

Actual total = calculate(sum(Data[amount]),data[type]="actual")

Variance = [budget]-[actual]

To your Table, visual, drag Month and Year from the Calendar table and the 3 measures written above.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors