Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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