cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors