Dividing one column in a table by one column in another table

[JohnM]

Hello,

I have a table (Balance) with a column [Actual]. I have another table (Expenses) with a column [Actual]

I want to divide (Balance[Actual]) by (Expenses[Actual]) = MonthReserves

I am new to DAX/Power Bi

After I figure that out I will want to divide (Balance[Actual]) by sum(Expense[Actual]) = ReserveRatio

Please help if you can.

Thanks,

John

[SqlJason]

I am assuming both the tables have some common columns, like Date or Account, etc.

In that case, you should add 1 extra table for each of the dimensions like Date or Account, and then relate both of your fact tables to that. once you have done that, you can see that if you choose a year from the date table, both the fact tables will be filtered.

Once you have done the above modelling change, now you can just create a simple measure

MonthlyReserves = sum(Balance[Actual])/sum(Expenses[Actual])

If you can give some sample data and what the results should look like, then I can use that to build a sample solution and give you some screenshots of the solution.

[JohnM]

Thank-you SqlJason

Here is an example:

I appreciate your help!

[JohnM]

My mistake ( I was a little hasty)

the formula is actually as outlined in the original problem and not b21/c21  but b21 from the first table and b21 from the second table.

Sorry for the confusion!

[SqlJason]

1) The first step would be to create a date table. If you don't have one, you can also create a calculated table in Power BI with the formula below

Date=DISTINCT(UNION(DISTINCT(Table1[date]), DISTINCT(Table2[date]))) 

2) Then create relations from both tables to your date table. Now if you make a pivot table with Dates, Actual from table1, Actual from Table2 on columns, you can see the appropriate values. This means that the new Date table is linked properly.

3) Once you have this, you can just write measures like M = A/B, and then drag and drop the new measure also to your table. It should work.

Let me know if you are having difficulty following this and if yes, I will try to post some screenshots also