cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Resolver II
Resolver II

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
Highlighted
Resolver II
Resolver II

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.

 

Highlighted
Helper I
Helper I

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?

Highlighted
Helper I
Helper I

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?

Highlighted
Resolver II
Resolver II

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

Highlighted
Helper I
Helper I

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

Perfect thank you!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors