calculate sum of aggregated averages ratio by category

[katelinf]

Hello,

I have HR abscence data by Employee (Step 1)

Absence ratio =  Absence hour / Worktime hour

How can I calculate the ratio absence of the Organization category (Step 3), which will be the sum of average ratios by Departments? (Step 2)

Step 1- Detailed raw data is by Employee:

Organization |	Dept. |	Employee |	Absence_hour |	Worktime_hour |	Abscence Ratio
O1	D1	E1	3	225	1,33%
O1	D1	E2	0	225	0
O1	D2	E3	2	225	0,89%
O1	D2	E4	3	225	1,33%
O1	D3	E5	5	225	2,22%

Step 2- Aggregated table by Department

Organization |	Dept. |	Abscence Ratio
O1	D1	0,67%
O1	D2	1,11%
O1	D3	2,22%

Step 3- Target: Aggregated table by Organization. Absence ratio will be sum of department averages.

Organization |	Abscence Ratio
O1	0,67+ 1,11 + 2,22 = %4

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Absence ratio expected measure: =
SUMX (
    VALUES ( Data[Dept.] ),
    CALCULATE ( SUM ( Data[Absence_hour] ) / SUM ( Data[Worktime_hour] ) )
)