Calculated column subtracting Real vs Budget

[Syndicate_Admin]

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

[v-yiruan-msft · Accepted solution]

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

[amitchandak]

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

Transpose: https://yodalearning.com/tutorials/power-query-helps-transposing-data/

Then you can create a measure of diff