Forum Discussion
Regionwise Allocation vs Available hours
in my power bi matrix table , i have employee in rows, allocation hours in columns, columns examples are North, South, East, West and total column. I have a available hours column as well. But the available hours are only available at total level i dont have region level information. What i want to achieve is have a row for employees and in the region columns show allocation hours in one value and (Allocation hours for North/Total available hours), (Allocation hours for South/Total available hours), (Allocation hours for East/Total available hours), (Allocation hours for West/Total available hours) and finally in the grand total column (total allocation hours/total available hours).
i dont have region columns separately all the regions are coming from single column in the table
i want each region hours and percentage to be shown differently. For example Row No-1 , Employee A, North Hours - 20, North % utilization (20/100=20%), South Hours-30, South % Utilization (30/100=30%), East hours -40, East % Utilization - (40/100=40%) , West hours - 10, West % Utilization (10/100=10%) and finally total allocation hours 100, total available hours 100, total utilization % 100%
Also, i cant bring available hours and allocation hours together in one table, because allocation for a week is split into different rwos because multiple projects are there. But available hours should be in single row only, if i bring available hours to combined table, available hours will be duplicated in to multiple rows
How do i achieve this
https://wetransfer.com/downloads/f205b04e55e00cf4733c33240e2bf17220231029150605/2444d2
7 Replies
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523hi, please find the link to sample file
I slightly rearranged your data model to understand it better.
Looking at this you can streamline by combining the Calendar and Holiday tables, and the Res Details table with the EmployeeOfficeLookup and TBL_New Resources. I'm a little baffled by the Tbl_New Weeks table. That should go into the calendar too, no?
adjusted the data model, combined the tables as you suggested , atatched the revised file, can you tell me how do i proceed now?
What's the primary key on the "Combined Resources" table?
There is UniqueresourceID in the resource table however duplicate entries are there. Does it work?
The main problem here is, for allocation hours , for a employee i have mulitple rows per week. But in the Avilable hours i have just for employee per week.
bumps
