topic Re: How to Calculate headcount difference between Actual and Budget in DAX Commands and Tips

How to Calculate headcount difference between Actual and Budget

bruosori — Fri, 06 May 2022 19:56:15 GMT

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

Re: How to Calculate headcount difference between Actual and Budget

dhruvinushah — Fri, 06 May 2022 17:18:49 GMT

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.

Re: How to Calculate headcount difference between Actual and Budget

Anonymous — Fri, 06 May 2022 17:23:15 GMT

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

Re: How to Calculate headcount difference between Actual and Budget

Anonymous — Fri, 06 May 2022 17:25:07 GMT

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

Re: How to Calculate headcount difference between Actual and Budget

bruosori — Fri, 06 May 2022 18:17:56 GMT

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

Re: How to Calculate headcount difference between Actual and Budget

dhruvinushah — Fri, 06 May 2022 21:54:56 GMT

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:

Re: How to Calculate headcount difference between Actual and Budget

tamerj1 — Sun, 08 May 2022 19:35:56 GMT

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]