cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Calculate Ratio from columns in two tables

I have two tables as per below

 Balance Year-Period Total 2021-01 872754 2021-02 849699 2021-03 846236 2021-04 804638 2021-05 797641 2021-06 820140 2021-07 797847 2021-08 795613 2021-09 778706 2021-10 783681 2021-11 807549 2021-12 807932 2022-01 801786

 Values Year-Period Turnover 2021-01 194866 2021-02 241215 2021-03 219672 2021-04 217108 2021-05 198586 2021-06 169087 2021-07 166488 2021-08 204115 2021-09 198671 2021-10 202633 2021-11 229480 2021-12 206510 2022-01 184861

So the end result should be something like

 Values Year-Period Value Ratio 2021-01 194866 0.223277 2021-02 241215 0.283882 2021-03 219672 0.259587 2021-04 217108 0.269821 2021-05 198586 0.248966 2021-06 169087 0.206168 2021-07 166488 0.208671 2021-08 204115 0.256551 2021-09 198671 0.255129 2021-10 202633 0.258565 2021-11 229480 0.284169 2021-12 206510 0.255603 2022-01 184861 0.230561

I need to calculate a ratio by dividing the amount in the Turnover column in the Value table, by the Total Column in the Balance Table (for each row). I have created a relationship between the tables (based on the Year-Period) column.

The Balance column is actually a calculated column (but I dont believe that should make a difference) and both tables actually have other columns I am using for different visulisations

This should be simple, (and it is in sql or Excel), but I am new to PowerBI and I am struggling. I can calculate a ratio of the totals of each column, but not a row by row result

1 ACCEPTED SOLUTION
Super User

you can try this

``Column = 'values'[Turnover]/ RELATED(balance[Total])``

pls see the attachment below

Proud to be a Super User!

3 REPLIES 3
Super User

you can try this

``Column = 'values'[Turnover]/ RELATED(balance[Total])``

pls see the attachment below

Proud to be a Super User!

Frequent Visitor

Thank you !!

It was the "Related" part I was missing

Super User

you are welcome

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors