Utilization by Department

[Anonymous]

Hi Everyone,

In company, we are using Timesheet Application. We are taking their datas from sharepoint. And visualizing them at Power BI. So each week new datas are coming from personnal. Here I have a Calculated Table. In this table, I am measuring utilization by personnal. These are utilization of the personnal by week. However, I also need to measure them by Department. Each week measuring by itself. So it is empty, if that week didn't come.

Personal	Department	Job	Week 1	Week 2	Week 3	Week 4	…	Week 53
A	Research	Engineer	0.99	0.45	0.38	0.44
B	Research	Technician	0.73	0.36	0.71	0.36
C	Sales	Service	0.34	0.71	0.41	0.61
D	Research	Engineer	0.46	0.56	0.39	0.57
E	Research	Engineer	1.23	0.37	0.94	0.89

DAX is like that;

Table =
SUMMARIZE (
RateListBU;
RateListBU[Birim];RateListBU[Rol];RateListBU[Personel];
"Week 1"; DIVIDE (
CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 1");
SUM ( RateListBU[Week 1] )
);
"Week 2"; DIVIDE (
CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 2");
SUM ( RateListBU[Week 2] )
);
"Week 3"; DIVIDE (
CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 3" );
SUM ( RateListBU[Week 3] )
);
"Week 4"; DIVIDE (
CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 4" );
SUM ( RateListBU[Week 4] )
);
.

.

"Week 53"; DIVIDE (
CALCULATE (SUM ( TimeSheetPlusListBU[Billable Hours] ); TimeSheetPlusListBU[Period] = "Week 53" );
SUM ( RateListBU[Week 53] )
);
)

With each week, new week utilization value by personnal is creating. How can I calculate utilize by departmant?

[sturlaws]

Hi @Anonymous 

how is your data shaped when you recieve it?

And how do you want do display the data? A table visual?

Having such a long switch statement as you have is cumbersome, and I am pretty sure it can be avoided.

Cheers,
Sturla