Percentage contribution to headcount

[Enan]

Dear All,
Please assit to make a DAX measure to drive % of headcount that what is the contribution of not avaialbe staff on daily headcount.

Calculation in Excel

In the above table I want to the percentage on each day in power BI.

I have two table in Power Bi as:

FACT_Codes

DIM_Headcount

Below screenshot show the data structure of each table.

FACT_Codes

DIM_Headcount

Thank you for your help.

[Enan]

Thanks alot dear brother for detailed explanation.

Let me try, i will update you.

[bolfri · Accepted solution]

Data source

fact_codes

Date	Staff Number	Code
2022-01-01	123456	SICK
2022-01-01	123457	COVP
2022-01-01	123458	COVP
2022-01-01	123459	COVP
2022-01-01	123460	COVP
2022-01-01	123461	SICK
2022-01-02	123462	SICK
2022-01-02	123463	SICK
2022-01-02	123464	SICK
2022-01-02	123465	SICK
2022-01-02	123466	SICK
2022-01-02	123467	SICK
2022-01-02	123468	SICK
2022-01-02	123469	SICK
2022-01-03	123470	COVP
2022-01-03	123471	COVP
2022-01-03	123472	COVP
2022-01-03	123473	COVP
2022-01-03	123474	COVP
2022-01-03	123475	SICK
2022-01-03	123476	SICK
2022-01-03	123477	SICK
2022-01-03	123478	SICK
2022-01-03	123479	SICK
2022-01-03	123480	SICK

dim_headcount

Date	Headcount
2022-01-01	423
2022-01-02	440
2022-01-03	429

Preparing data

fact_codes

Date - date (not datetime, not datetimezone)

Staff number - number

Code - text

= Table.TransformColumnTypes(Source,{{"Date", type date}, {"Staff Number", Int64.Type}, {"Code", type text}})

dim_headcount

Date - date (not datetime, not datetimezone)

Headcount - number

= Table.TransformColumnTypes(Source,{{"Date", type date}, {"Headcount", Int64.Type}})

Preparing model

Common field in both tables are dates, so we need calendar to connect them correctly. You can use any calendar that you want, but my recommendation is to create dim_calendar tables in DAX using Modeling > New Table option.

dim_calendar = 
CALENDAR(
    MIN(FIRSTDATE(fact_code[Date]),FIRSTDATE(dim_headcount[Date])),
    MAX(LASTDATE(fact_code[Date]),LASTDATE(dim_headcount[Date]))
)

This DAX creates a table that holds all the dates between minimum and maximum date in fact_core or fact_headcount table. With this approach, you will prevent situation, when new dates will come to eg dim_headcount table that won't appear in the fact_code and vice-versa.

Important note! Make sure to select new created column "Date" and change Type from Datetime to Date.

Notice: for displaying data I am using yyyy-mm-dd format, but you can change it as you prefer.

Preparing relationship

In the model view connect:

• fact_table[date] to dim_calendar[date] - Power BI should automatically find out that this is many to one relationship
• dim_headcount[date] to dim_calendar[date] - Warning! You need to change this relationship, because Power BI will set that to be 1:1 and Cross-Filterdirection set to Both. Change it to be Many to One, from dim_calendar to dim_headcount. This changes are marked as green on the screenshot.

Bonus #1 - you can turn off the Dates in fact_table and dim_headcount table clicking on "eye" next to them. Remember to always use Date column from dim_calendar table. Disable visibility of other dates saves you from making mistakes. This changes are marked as blue on the screenshot.

Bonus #2 - Before next step: I always create a new table that holds my measures in one place. You can use code to create a dummy table like this:

DAX = ROW("DAX","DAX")

Protip: After creating at least one measure in that table you can make this "DAX" column invisible like the dates before.

Screenshot before creating measures:

Preparing measures

Total not avaliable Staff = COUNT(fact_code[Staff Number])

Headcount measure = SUM(dim_headcount[Headcount])

 Not that I cannot make a measure with name Headcount, because we already have a Column with that name. You need to change that name in the source table or change the name of a measure that meets your needs.

% of not available = DIVIDE([Total not avaliable Staff],[Headcount measure])

In this measure we do not multiply this by 100! If we want percentage we're only changing format. You can change the format by clicking on the measure and clicking on the icon of % or selecting Percentage format from list.

Last step - putting all together

Create a table and put there the measures that you've created and a date from dim_calendar table.

Final result:

PBIX FILE: https://we.tl/t-yf6SecDkCD