cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dombarg Helper II
Helper II

how to calculate ratio between two table

hello. I have two tables in which there are many accouting records from accounting journal.

many.JPG

 

 

 

 

I want to calculate ratio between left table (account number 112) and right table (account 8520 ). indeed it should be calculated as (account 112) / (account 8520)).

what i did was to create a new query from left table in which I make summarization by account number "112" and then make a physical relation via Bridge table to right table.

But I have about 103 ratio between diffrent accounts!!! and hence it is not rational to create a new query for each of them.

instead,I want to summarize left table by special account number (for each ratio) via DAX and then make a virtual relation between that summurized table to the right table.

How can i do that?

i think there should be a optimal way.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: how to calculate ratio between two table

Hi @dombarg

I make a test to describe what I don’t understand.

1.“I have about 103 ratio between diffrent accounts”

It means you need make summarization by account number "112",”113”,”114”……   right?

column “sum1” will get the sum of amount left table based on different account.

column “sum2” will get the sum of amount right table based on different account.

sum1 = CALCULATE(SUM(Sheet1[amount]),ALLEXCEPT(Sheet1,Sheet1[account]))

sum2 = CALCULATE(SUM(Sheet2[amount]),ALLEXCEPT(Sheet2,Sheet2[account]))

Then for ratio=A/B

A-> sum1

B-> sum2

 

2.“make a physical relation via Bridge table to right table”

I don’t know what’s the physical relation via Bridge table to right table.

I manage the relationship between left table and right table with column “ID”, as this column contains mutiple repetitive values, so I make a bridge table with each unique value.

1.png

 

 

Best Regards

Maggie

View solution in original post

5 REPLIES 5
Community Support
Community Support

Re: how to calculate ratio between two table

Hi @dombarg

What exactly is the rule to calculate ratio between left table and right table?

Does the following example meet your requirement?

1.png

 

Best Regard

Maggie

dombarg Helper II
Helper II

Re: how to calculate ratio between two table

thank u @v-juanli-msft .

please consider following DAX : 

 

 

ratio =
DIVIDE (
    CALCULATE ( [sum of amounnt left table], account = 112 ),
    CALCULATE ( [sum of amounnt right table], account = 8520 )
)

it will be reapeted for another accounts.for example :

124/8790 and etc....

year as slicer and citiy as axis bar.

(right table has many duplicated avalue too.but in the screenshut  it is uniqe)

dombarg Helper II
Helper II

Re: how to calculate ratio between two table

no idea?

Community Support
Community Support

Re: how to calculate ratio between two table

Hi @dombarg

I make a test to describe what I don’t understand.

1.“I have about 103 ratio between diffrent accounts”

It means you need make summarization by account number "112",”113”,”114”……   right?

column “sum1” will get the sum of amount left table based on different account.

column “sum2” will get the sum of amount right table based on different account.

sum1 = CALCULATE(SUM(Sheet1[amount]),ALLEXCEPT(Sheet1,Sheet1[account]))

sum2 = CALCULATE(SUM(Sheet2[amount]),ALLEXCEPT(Sheet2,Sheet2[account]))

Then for ratio=A/B

A-> sum1

B-> sum2

 

2.“make a physical relation via Bridge table to right table”

I don’t know what’s the physical relation via Bridge table to right table.

I manage the relationship between left table and right table with column “ID”, as this column contains mutiple repetitive values, so I make a bridge table with each unique value.

1.png

 

 

Best Regards

Maggie

View solution in original post

dombarg Helper II
Helper II

Re: how to calculate ratio between two table

Wow thank u.it is so simple.i thought it should be very complex 😄 .thank u.i made some changes to your answer:

 

test for new method =
DIVIDE (
    CALCULATE ( SUM ( 'bs'[amount] ), 'bs'[account] = "8520" ),
    CALCULATE ( SUM ( 'INCOME'[amount] ), 'INCOME'[account] = "102" )
)

 

 

just,would u please explain how filter in this Bridge flows through tables?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors