Forum Discussion
Calculating UR summary in Weekly forecast data
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.
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 _tableTable 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
3 Replies
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.
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 _tableTable 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
