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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors