Duplicated records - fixed row

[Nethergate]

Happy new year guys. I have a bit of a sticker issue per below.

• Background: I have the below source data with the cost by Account and headcount is based on cost center.
• Objective: I wish to derive the cost per HC such that I can change the view to easily for analysis
• Issue:
  • However, the problem is that the headcount by cost center is duplicative due to multiple Account type in the records. For example, physically in Feb, I only have 5 headcount in Cost Center C. But PowerBi would sum up the records and show me 10 headcount, which is incorrect
  • Another example is physically in Jan, I only have 1 headcount in Australia but PowerBi would sum up the records and show me 2 headcount, which is incorrect
  • I wish to be able to change the view freely so that I can see the cost per FTE by different cuts for analysis

Really appreciate your help on this ! 

Source data

Source data (editable version)

Country	Cost Center	Month	Account	Cost	Headcount by cost center	Cost per HC
Australia	A	Jan	payroll	100	1	100
Australia	A	Jan	bonus	100	1	100
Australia	A	Feb	payroll	200	3	67
Australia	B	Feb	payroll	200	4	50
Singapore	C	Feb	payroll	200	5	40
Singapore	C	Feb	bonus	200	5	40
Correct Total			1,000	13	77
Incorrect total			1,000	19	53

Correct Desired view 1

Correct Desired view 2

[v-yangliu-msft · Accepted solution]

Hi  @Nethergate ,

Here are the steps you can follow：

Correct Desired view 1：

1. Use Enter data to create a table

2. Create measure.

Sum_Month_Jan =
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])&&'Table'[Month]="Jan"))

Sum_Month_Feb =
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])&&'Table'[Month]="Feb"))

Sum_Month_group = CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])))

Sum—Headcount-month_Jan =
CALCULATE(MAX('Table'[Headcount by cost center]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])&&'Table'[Month]="Jan"))

Sum—Headcount-month_Feb =
CALCULATE(MAX('Table'[Headcount by cost center]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])&&'Table'[Month]="Feb"))

Sum—Headcount-month-group =
[Sum—Headcount-month_Jan] + [Sum—Headcount-month_Feb]

sum_costperHc_month_Jan =
CALCULATE(SUM('Table'[Cost per HC]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])&&'Table'[Month]="Jan"))

sum_costperHc_month_Feb =
CALCULATE(SUM('Table'[Cost per HC]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])&&'Table'[Month]="Feb"))

sum_costperHc_month_group =
CALCULATE(SUM('Table'[Cost per HC]),FILTER(ALL('Table'),'Table'[Cost Center]=MAX('Table'[Cost Center])))

Flag =

VAR _1=
SWITCH(
TRUE(),
SELECTEDVALUE('Slice'[Index]) =1,[Sum_Month_Jan],
SELECTEDVALUE('Slice'[Index]) =2,[Sum_Month_Feb],
SELECTEDVALUE('Slice'[Index]) =3,[Sum_Month_group],
SELECTEDVALUE('Slice'[Index]) =4,[Sum—Headcount-month_Jan],
SELECTEDVALUE('Slice'[Index]) =5,[Sum—Headcount-month_Feb],
SELECTEDVALUE('Slice'[Index]) =6,[Sum—Headcount-month-group],
SELECTEDVALUE('Slice'[Index]) =7,[sum_costperHc_month_Jan],
SELECTEDVALUE('Slice'[Index]) =8,[sum_costperHc_month_Feb],
SELECTEDVALUE('Slice'[Index]) =9,[sum_costperHc_month_group])
RETURN
IF( ISBLANK([Sum_Month_Jan])&&ISBLANK([Sum_Month_group])&&ISBLANK([Sum—Headcount-month_Jan])&&ISBLANK([Sum—Headcount-month-group])&&ISBLANK([sum_costperHc_month_Jan]),BLANK(),_1)

3. Select [HybridColumns] column, click [Column tools] – Sort by column – [lndex]

4. Result:

Correct Desired view 2：

1. Use Enter data to create a table

2. Create measure.

Country_Month_Jan =
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])&&'Table'[Month]="Jan"))

Country_Month_Feb =
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])&&'Table'[Month]="Feb"))

Country_Month_group = CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])))

Country_Headcount-month_Jan =
CALCULATE(MAX('Table'[Headcount by cost center]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])&&'Table'[Month]="Jan"))

Country_Headcount-month_Feb =
IF(
    MAX('Table'[Country])="Australia",
CALCULATE(SUM('Table'[Headcount by cost center]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])&&'Table'[Month]="Feb")),
CALCULATE(MAX('Table'[Headcount by cost center]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])&&'Table'[Month]="Feb")))

Country_Headcount-month-group =
[Country_Headcount-month_Jan]+[Country_Headcount-month_Feb]

Country_costperHc_month_Jan =
IF(
    MAX('Table'[Country])="Australia",BLANK(),"#DIV/0!")

Country_costperHc_month_Feb =
CALCULATE(SUM('Table'[Cost per HC]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])&&'Table'[Month]="Feb"))

Country_costperHc_month_group =
CALCULATE(SUM('Table'[Cost per HC]),FILTER(ALL('Table'),'Table'[Country]=MAX('Table'[Country])))

Flag2 =
VAR _1=
SWITCH(
TRUE(),
SELECTEDVALUE('Desired view'[Index])=1,[Country_Month_Jan],
SELECTEDVALUE('Desired view'[Index]) =2,[Country_Month_Feb],
SELECTEDVALUE('Desired view'[Index]) =3,[Country_Month_group],
SELECTEDVALUE('Desired view'[Index]) =4,[Country_Headcount-month_Jan],
SELECTEDVALUE('Desired view'[Index])=5,[Country_Headcount-month_Feb],
SELECTEDVALUE('Desired view'[Index])=6,[Country_Headcount-month-group],
SELECTEDVALUE('Desired view'[Index])=7,[Country_costperHc_month_Jan],
SELECTEDVALUE('Desired view'[Index])=8,[Country_costperHc_month_Feb],
SELECTEDVALUE('Desired view'[Index])=9,[Country_costperHc_month_group])
RETURN
IF( ISBLANK([Country_costperHc_month_Jan])&&ISBLANK([Country_costperHc_month_group])&&ISBLANK([Country_Headcount-month_Jan])&&ISBLANK([Country_Headcount-month-group])&&ISBLANK([Country_costperHc_month_Jan]),BLANK(),_1)

3. Select [HybridColumns] column, click [Column tools] – Sort by column – [lndex]

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly