Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following table below:
Date | Type | Headcount |
31/01/2022 | Budget | 3 |
31/01/2022 | Actual | 2 |
28/02/2022 | Budget | 5 |
28/02/2022 | Actual | 7 |
How can I create a column name "Deviation" that shows the difference between actual and budget?
Expected:
Jan | Feb | ||||
Actual | Budget | Deviation | Actual | Budget | Deviation |
2 | 3 | -1 | 5 | 7 | -2 |
I am trying the calcuate function to create a measure however the deviation field appears duplicated for each type (Actual/Budget)
Deviation:=CALCULATE(Sum[Headcount];[Type]="Actual")-CALCULATE(Sum[Headcount];[Type]="Budget")
Result:
Jan | Feb | ||||||
Actual | Deviation | Budget | Deviation | Actual | Deviation | Budget | Deviation |
2 | -1 | 3 | -1 | 5 | -2 | 7 | -2 |
Hi @bruosori
Two ways of doing that https://www.dropbox.com/t/n6fnM12rCaJWnQLd
First method using a matri visul with single measure
Count =
VAR NormalCount =
SUM ( 'Table'[Headcount] )
VAR BudgetCount =
CALCULATE (
SUM ( 'Table'[Headcount] ),
'Table'[Type] = "Budget"
)
VAR ActualCount =
CALCULATE (
SUM ( 'Table'[Headcount] ),
'Table'[Type] = "Actual"
)
VAR DeviationCount =
ActualCount - BudgetCount
RETURN
IF (
HASONEVALUE ( 'Table'[Type] ),
NormalCount,
DeviationCount
)
2nd method using a table visual and 3 mesures
Actual =
CALCULATE (
SUM ( 'Table'[Headcount] ),
'Table'[Type] = "Actual"
)
Budget =
CALCULATE (
SUM ( 'Table'[Headcount] ),
'Table'[Type] = "Budget"
)
Deviation = [Actual] - [Budget]
Hi, I think you mean difference instead of deviation. Try to make a calculated column "month" and create a table with this measure
Difference =
var actual = CALCULATE(SUM(Table[Headcount]), Table[Type] = "Actual")
var budget = CALCULATE(SUM(Table[Headcount]), Table[Type] = "Budget")
return actual - budget
Hi @bruosori ,
try creating a measure for your Deviation Calculation as follows:
Deviation = SUMX('yourTableName', [Actual] - [Budget])
Use that Deviation field in your table or matrix in Power BI.
I don't have the columns Actual and Budget. See my table structure.
Hi @bruosori
I would create 3 measures:
//Measure 1
Actuals = CALCULATE(SUM(TestTable[Headcount]), TestTable[Type] = "Actual")
//Measure 2
Budgets = CALCULATE(SUM('TestTable'[Headcount]),'TestTable'[Type] = "Budget")
//Measure 3
Deviations = [Actuals] - [Budgets]
Once you have created these 3 measures, convert the "Date" column into a Date datatype from Text.
You can then put the measures in a table as follows:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |