How can I calculate Occupancy in percentage?

[Anonymous]

@Anonymous

Hello everyone, 

I have two tables: Demand and Capacity, in both tables I have columns like FTE, Hours, Date, Months, etc… now I want to know Occupation of FTE per department and that can be calculated as (Demand [FTE]/ Capacity[FTE]) * 100. So, I created a new measure called as Occupation by using the formula as Occupation = SUM(Demand[FTE])/SUM(Capacity[FTE])*100

But it gives me incorrect values as it does not match with Department and the year. Can you please help me for getting correct values as my result. 

I am even interested to get the Total value where Total means Capacity[FTE] - Demand[FTE] and it must be according to Department. Please help me about this as well. How can I get it?  

And I want result as per below format. In the rows Capacity and Demand, I want FTEs and I have FTE for both in seperate tables 

Thank you so much in advance.

Departments		January	February	March	.........(other months)
Mechanical	Capacity
Mechanical	Demand
Mechanical	Occupation
Electrical	Capacity
Electrical	Demand
Electrical	Occupation

[Anonymous]

As I'm thinking about your situation, I wonder if you would be better off combining the tables rather than trying to relate them, since you are combining demand and capacity but, I'm assuming, have the same columns for both? If so, you might consider just combining them by either merging or appending queries.

If not, consider using a "LOOKUPVALUE" function in a calculated column on one of your tables to look up the relevant values from the other and put them on the same table.

[Anonymous]

Based on how you describe the error, I think this might be a relationship issue.

Do you have a date table that you are using between your two tables? That might be the issue if your visualization is only using one of the date columns from your two tables instead of a shared date axis.

If you haven't created one, the easiest way to do so is to select "New Table" in the tables view and enter the function "Date = CalendarAuto()"

Then drag relationships between your two tables' date columns and the new date table you created.

Then when you build the visualization, use the date table as your axis.

[Anonymous]

I tried the way right now by creating a new table called as Date with the meausre but it is giving me false values again. Can you please suggest me any other way? It will really very helpful. Thanks in advance

[Anonymous]

Cannot I connect Date columns from both tables rather than creating a new table just for Date? 

Thank you in advance