Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
@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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |