cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Calculated column subtracting Real vs Budget

Good, I'm new to the community. I'm putting together a table from a database I have where there are rows that correspond to actual periods and rows to budget periods. I show you an example:

 Period Frequency Income Cost Result Real 20 200 140 60 Real 20 200 140 60 Real 20 200 140 60 Real 20 200 140 60 Real 20 200 140 60 Budget 30 200 125 75 Budget 30 200 125 75 Budget 30 200 125 75 Budget 30 200 125 75

I would need to put together a summary table where the real and the budget are exposed as columns, and have a 3rd column that compares me these 2 periods:

 Value Real Budget Dif. Real vs Budget Frequency 100 120 -20 Income 1000 800 200 Cost 700 500 200 Result 300 300 0

I do not know how to generate a calculated column that basically subtracts the real values vs the budget values, but without being associated with a specific field, I would need this calculation to apply to all the fields that the original table has. I hope I have been clear.

Thank you very much for your help

Santiago

1 ACCEPTED SOLUTION
Community Support

Hi @SantiPeri ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Unpivot the selected columns:Frequency, Income, Cost and Result in Power Query Editor

2. Create a measure as below

``````Dif. Real vs Budget =
VAR _diff =
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Real" )
- CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Budget" )
RETURN
IF ( ISINSCOPE ( 'Table'[Period] ), SUM ( 'Table'[Value] ), _diff )``````

3. Create a matrix visual(Rows: Type   Columns: Period   Values: Dif. Real vs Budget)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

Hi @SantiPeri ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Unpivot the selected columns:Frequency, Income, Cost and Result in Power Query Editor

2. Create a measure as below

``````Dif. Real vs Budget =
VAR _diff =
CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Real" )
- CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Period] = "Budget" )
RETURN
IF ( ISINSCOPE ( 'Table'[Period] ), SUM ( 'Table'[Value] ), _diff )``````

3. Create a matrix visual(Rows: Type   Columns: Period   Values: Dif. Real vs Budget)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@Syndicate_Admin , group by and  Transpose the data in power query and try

Then you can create a measure of diff

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors