Forum Discussion

dombarg's avatar
dombarg
Icon for Helper II rankHelper II
8 years ago
Solved

how to calculate ratio between two table

hello. I have two tables in which there are many accouting records from accounting journal.         I want to calculate ratio between left table (account number 112) and right table (accou...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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.

     

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

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.

 

 

Best Regards

Maggie

dombarg's avatar
dombarg
Icon for Helper II rankHelper II
8 years ago

Wow thank u.it is so simple.i thought it should be very complex :D .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?