IF - SUM - Utilization Table

[Anonymous]

Hi Everyone,

In company, we are using Timesheet Application. We are taking their datas from sharepoint. 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 5	...	...	Week 53
A	Research	Engineer	0.99	0.45	0.38	0.44	0.22
B	Research	Technician	0.73	0.36	0.71	0.36	0.34
C	Sales	Service	0.34	0.71	0.41	0.61	0.93
D	Research	Engineer	0.46	0.56	0.39	0.57	0.65
E	Research	Engineer	1.23	0.37	0.94	0.89	0.45

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 4" );

SUM ( RateListBU[Week 4] )

);

)

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

[Stachu]

are you OK with changing your data model? This problem is very easy to solve if instead of creating a Table you create a measure and use Matrix visual to display it as a table. It would require the RateListBU table to look like this (you could unpivot it in Power Query)

Personal	Department	Job	Week	Value
A	Research	Engineer	Week 1	0.99
A	Research	Engineer	Week 2	0.45
A	Research	Engineer	Week 3	0.38
A	Research	Engineer	Week 4	0.44
A	Research	Engineer	Week 5	0.22
A	Research	Engineer	Week 53