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

Divide a value in one column by the corresponding value in another

Hi - I have 2 tables, one containing a PRODUCT_VOLUME column and one containing a REGISTER_SIZE column. Both columns are linked by LEGAL_ID as the unique identifier (an additional column in both tables)

 

I want create a new column (as there is one row per month to this data and want to show it on a time chart) to divide the PRODUCT_VOLUME for the specific LEGAL_ID by the REGISTER_SIZE for that specific LEGAL_ID. 

 

I'm a bit of a novice and so if anyone can help with the DAX that would be appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
ccapowerbi Member
Member

Re: Divide a value in one column by the corresponding value in another

Hey @lewdow 

 

I should have used LOOKUP value; sorry about that. 

I tested this out & here is my DAX for the calculated column:

Product/Register = 
DIVIDE(
    Product_Volume_Table[Product_Volume],
    LOOKUPVALUE(
        Register_Size_Table[Register_size],
        Register_Size_Table[Legal_id],
        Product_Volume_Table[Legal_id]
    )
)

DAX screenshot.PNG

 

 

 

 

 

 

 

Keep in mind I don't know the name of your tables, so the name of my tables are probably different than yours. I'm including my pbix file so you can see how I have things set up. 

Click HERE to access my pbix file.

View solution in original post

5 REPLIES 5
ccapowerbi Member
Member

Re: Divide a value in one column by the corresponding value in another

@lewdow 

 

Hey!

 

When I have two tables (A & B) and want to reference a value in a column in table B in a calculated column in table A when they are related, I use the LOOKUP expression. 

 

Maybe in your situation you can use something like this:

New Column =
DIVIDE(
  PRODUCT_VOLUME,
  LOOKUP(
     REGISTER_SIZE table[REGISTER_SIZE column],
     REGISTER_SIZE table[LEGAL_ID column],
     PRODUCT_VOLUME table[LEGAL_ID column]
     )
)

Something like this could be used if adding a calculated column in the table that contains the PRODUCT_VOLUME column.

 

lewdow Frequent Visitor
Frequent Visitor

Re: Divide a value in one column by the corresponding value in another

Thanks, can't quite get it to work. Shoudl it be LOOKUPVALUE instead of just LOOKUP?

lewdow Frequent Visitor
Frequent Visitor

Re: Divide a value in one column by the corresponding value in another

The error I get when using LOOKUPVALUE appears to not be able to find the REGISTER_SIZE table - is there something special I need to add in to get it to recognise looking at another table?

ccapowerbi Member
Member

Re: Divide a value in one column by the corresponding value in another

Hey @lewdow 

 

I should have used LOOKUP value; sorry about that. 

I tested this out & here is my DAX for the calculated column:

Product/Register = 
DIVIDE(
    Product_Volume_Table[Product_Volume],
    LOOKUPVALUE(
        Register_Size_Table[Register_size],
        Register_Size_Table[Legal_id],
        Product_Volume_Table[Legal_id]
    )
)

DAX screenshot.PNG

 

 

 

 

 

 

 

Keep in mind I don't know the name of your tables, so the name of my tables are probably different than yours. I'm including my pbix file so you can see how I have things set up. 

Click HERE to access my pbix file.

View solution in original post

lewdow Frequent Visitor
Frequent Visitor

Re: Divide a value in one column by the corresponding value in another

Perfect thank you!

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors