cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
David_F64
Frequent Visitor

Calculate Ratio from columns in two tables

I have two tables as per below

Balance
Year-PeriodTotal
2021-01872754
2021-02849699
2021-03846236
2021-04804638
2021-05797641
2021-06820140
2021-07797847
2021-08795613
2021-09778706
2021-10783681
2021-11807549
2021-12807932
2022-01801786

 

Values
Year-PeriodTurnover
2021-01194866
2021-02241215
2021-03219672
2021-04217108
2021-05198586
2021-06169087
2021-07166488
2021-08204115
2021-09198671
2021-10202633
2021-11229480
2021-12206510
2022-01184861

 

So the end result should be something like

Values 
Year-PeriodValueRatio
2021-011948660.223277
2021-022412150.283882
2021-032196720.259587
2021-042171080.269821
2021-051985860.248966
2021-061690870.206168
2021-071664880.208671
2021-082041150.256551
2021-091986710.255129
2021-102026330.258565
2021-112294800.284169
2021-122065100.255603
2022-011848610.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
ryan_mayu
Super User
Super User

@David_F64 

you can try this

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

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
ryan_mayu
Super User
Super User

@David_F64 

you can try this

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

1.PNG

pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you !!

It was the "Related" part I was missing

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.