Forum Discussion
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
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
2 Replies
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
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
