How to Calculate headcount difference between Actual and Budget

[bruosori]

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

[tamerj1]

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]

[Anonymous]

Hi, I think you mean difference instead of deviation. Try to make a calculated column "month" and create a table with this measure

[Anonymous]

Difference = 

var actual = CALCULATE(SUM(Table[Headcount]), Table[Type] = "Actual")
var budget = CALCULATE(SUM(Table[Headcount]), Table[Type] = "Budget")

return actual - budget

[dhruvinushah]

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. 

[bruosori]

I don't have the columns Actual and Budget. See my table structure.

[dhruvinushah]

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: