DIVIDE (DAX) yields incorrect results with numerator and denominator in separate tables

Question

I have the below data model that utilizes the one-to-many relationship of the field 'Date" in red. I need the blue square to be the numerator and the green square to be the denominator. It's also worth noting that both fact tables have five entries for each date (one entry for each building).

Ultimately I need to show this and three other measures that are the same (different data such as capacity figures instead of allocation figures) in a clustered column chart. The chart is separated by the 'Building' field as shown below (I'm clipping off the building names at the bottom purposely.) My expectation is that this will show the results based on the entire dataset, then I can use date filters such as month and/or day of the week to filter down to targeted data as needed.

My problem is that these values are incorrect by quite a bit. The Peaks should be showing anywhere from 50%-80% and the Averages should be 20%-40%.

I tried the following DAX:

Avg Allocated Utilization % = DIVIDE( SUM('Utilization Report'[Average Person Count]), SUM('Capacity and Allocation'[Allocated]), 0 )

The source data is clean and I've tried many different angles for the DAX with no luck. I assume that the issue is I'm not using the correct DAX. Thank you in advance for the guidance!

Answer 1

You will need a new Dimension table for Building and then create relationships with your two Fact tables Utilization Report and Capacity and Allocation. Finally, you need to use the new Building column in the new table for your visuals.

The issue you have currently is that you are most likely using Building in your Utilization Report table which will give you the correct numerator; but your denominator will be the sum across all Buildings.