Calculating UR summary in Weekly forecast data

[Anonymous]

Hi, 

I am having a forecast data table that I need to summarize in a report everyweek for the whole year. The data is something like this: 

I am calculating UR on actual and available hours and the pivot table output is something like this: 

This is achieved using calculated item in pivot table however I am not able to figure out how to add calculated rows in power BI. I am very new to this though, so reaching out to experts out there for help. Thanks in anticipation. 

Regards.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

123_table =
VAR _table=SUMMARIZE('Table',
"ResourceName",SELECTCOLUMNS('Table',"1",MAX('Table'[Resource Name])),
"Role",SELECTCOLUMNS('Table',"Role",MAX('Table'[Role])),
"User ID",SELECTCOLUMNS('Table',"User ID",MAX('Table'[User ID])),
"Work Type",SELECTCOLUMNS('Table',"Work Type","UR on Actual Hours"),
"Project Number/Name",SELECTCOLUMNS('Table',"Project Number/Name",MAX('Table'[Project Number/Name])),
"W1",SUMX(FILTER(ALL('Table'),[Work Type]="Billable Project"),[W1])/SUMX(FILTER(ALL('Table'),[Work Type]="Actual Hours"),[W1]),
"W2",SUMX(FILTER(ALL('Table'),[Work Type]="Billable Project"),[W2])/SUMX(FILTER(ALL('Table'),[Work Type]="Actual Hours"),[W2]),
"W3",SUMX(FILTER(ALL('Table'),[Work Type]="Billable Project"),[W3])/SUMX(FILTER(ALL('Table'),[Work Type]="Actual Hours"),[W3]),
"W4",SUMX(FILTER(ALL('Table'),[Work Type]="Billable Project"),[W4])/SUMX(FILTER(ALL('Table'),[Work Type]="Actual Hours"),[W4]),
"W5",SUMX(FILTER(ALL('Table'),[Work Type]="Billable Project"),[W5])/SUMX(FILTER(ALL('Table'),[Work Type]="Actual Hours"),[W5]),
"W6",SUMX(FILTER(ALL('Table'),[Work Type]="Billable Project"),[W6])/SUMX(FILTER(ALL('Table'),[Work Type]="Actual Hours"),[W6]))
return
_table

Table 2 = UNION('Table','123_table')

2. Result:

Regarding "UR ON Avaliable Hours", I am not very clear about the result logic, you can follow the above steps to create a table, and then merge it with Table 2, you can get.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thanks for your time on this, Liu Yang! 

UR on Available hours is similar to UR on Actual hours but leaves are reduced from the denominator.

Available hours = actual hours - leaves

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.