Absence KPIs

[Michalison]

I am doing a dashboard that has 2 tables the personnel table which has a staff number column and Absence table which also has a staff number column - I have created a realtionship from personnel table to absence  table using the staff number on a 1 to many as there is only 1 staff number per person on the personnel table and many absences per employee on the absence table. 

I need to do a chart with the calculation count of total absence days from absence table ( sliced by year and department) divided by personnel table count of employees but also based on the year and department sliced currently on the absence table.   So lets say resouces department has 113 people in 2021 (personnel table ) and resources department had 400 days absence in 2021 the calculation would be 400 divded by 113.  when I try this the numbers are all wrong because instead of 113 its calcualting on all the staff in the personnel table 800 as the slicers work for the absence table and not the personnel table - how do I get round this.  Many Thanks

[v-cgao-msft · Accepted solution]

Hi @Michalison ,

I made a sample according to the description with four tables which are personnel table，department table，absence table and calendar table.

Relationship.

Then try this measure.

Absence KPIs = 
VAR _KPIS = COUNTROWS ( 'absence table' ) / COUNTROWS ( 'personnel table' )
RETURN
IF(_KPIS = BLANK(),0,_KPIS)

The result should be like this and slicer also works fine.

Also, attached the pbix file as the reference.

Best Regards,

Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[v-cgao-msft · Accepted solution]

Hi @Michalison ,

I made a sample according to the description with four tables which are personnel table，department table，absence table and calendar table.

Relationship.

Then try this measure.

Absence KPIs = 
VAR _KPIS = COUNTROWS ( 'absence table' ) / COUNTROWS ( 'personnel table' )
RETURN
IF(_KPIS = BLANK(),0,_KPIS)

The result should be like this and slicer also works fine.

Also, attached the pbix file as the reference.

Best Regards,

Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Michalison · Accepted solution]

Thank you so much for your help this has worked