Calculation for matching

[Anonymous]

I have 2 tables, both have month names and the same location name. However, the tables cannot be merged, nor can I create a relationship between then because of many-to-many.

As an example:

Table1 contains values representing "Downtime"

	January	February	March	April	May
Dallas	1566	1914	3373	3360	3177
Austin	2112	2905	1727	3132	943
Houston	5645	1309	1849	1167	984

Table2 contains values representing "ScheduledTime"

	January	February	March	April	May
Dallas	2139	2288	2861	3128	2972
Austin	3649	3132	3447	3111	3128
Houston	3343	2622	3163	3117	3111

I need to create a measure that would be:

DIVIDE( DIVIDE( SUM(TABLE1[Downtime]), SUM(TABLE2[ScheduledTime))), 60)

This is not working though, the resulted numers are all wrong.  If I do it manually in the calculator, I get the correct result.  

So for January of Dallas, my answer should be

(1566 / 2139) / 60 = .0122

I also tried creating a column in Table2 using the same calculation, and the resulted value was the same the entire way down the table. 

How can I calculate this accurately? 

[jennratten · Accepted solution]

Hello - this is one way you can accomplish the result with Power Query...

Create a new table in Power Query that combines the two tables and performs the division.  

let
    Source = Table.NestedJoin (
        Table.UnpivotOtherColumns(Table1, {"Location"}, "Month", "Downtime"), 
        {"Location", "Month"}, 
        Table.UnpivotOtherColumns(Table2, {"Location"}, "Month", "ScheduledTime"), 
        {"Location", "Month"}, "Table", JoinKind.LeftOuter
    ),
    Expand = Table.ExpandTableColumn ( Source, "Table", {"ScheduledTime"}),
    #"Inserted Division" = Table.AddColumn(Expand, "Division", each [Downtime] / [ScheduledTime], type number)
in
    #"Inserted Division"