Divide sum of column with fixed value in Power PIvot

[kuzner]

Hello lovely Excel people,

I have a question on how to create a measure (measures) to calculate the division between sum of working hours from one table with fixed monthly working hours in another table.
Let me explain. In the sample file I am attaching, I have two tables. One is on the sheet input data where working hours for an employee on specific dates in a month are written. When a worker was actually working, number of hours he/she spent, on the project we are analysing, for that worker are written. In the other table (hours by month) there is information how much working hours (obviously calculated from working days in a month) an employee had to workin a specific month. The connection between two tables is in the column month year.

What I am trying to calculate is the share of hours a worker spent on our project in contrast to the hours he/she had to do in one month. Therefore the sum of hours in a month has to be divided by the complete working hours in a month. For example: if a worker in jan 2021 worked 100 hours on our project and his monthly quota is 168 hours, he spent almost 60% of his working time on the project (100/168*100%).

I know how to do it in Excel, but am stuck in a no man's land on how to do it in Power PIvot and later on in Power BI.

Thank you for your help already in advance.

Regards, MArko

[sandy666]

• Open the Power Pivot window in Excel.
• Click on the "Calculated Fields" button in the "Calculations" group on the "Home" tab.
• In the "Name" field, enter a name for the calculated field, for example, "DividedColumn".
• In the "Formula" field, enter the formula to divide the sum of the column by the fixed value, for example, "SUM([Column])/10" to divide the sum of the "Column" by 10.
• Click "OK" to create the calculated field.

Once the calculated field is created, you can use it in your PivotTable or PivotChart. Drag the calculated field to the Values area of the PivotTable or PivotChart and select the appropriate aggregation function (such as "Sum" or "Average") to display the result of the calculation.