Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SuKarEng
New Member

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: 

SuKarEng_0-1642070104062.png

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

SuKarEng_1-1642070305759.png

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.

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @SuKarEng ,

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:

vyangliumsft_0-1642393941147.png

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

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @SuKarEng ,

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:

vyangliumsft_0-1642393941147.png

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

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
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.