Multiply 2 columns from 2 different tables

[TheITAnalyst13]

Hi Team,

I'm new in Power BI and i've been struggling doing few things. My only teachers are videos and forums online, I can't seem to crack this one thing.

I have 2 columns from 2 tables which i want to multiply:

Shortage column is based on a formula:

Shortage = ('Overdeployment Table'[Regional Entitlement]-'Overdeployment Table'[Regional Deployment])

I want to multiply the Shortage column and the Unit Price column to have the Total Amount column. However, i am getting the error below as soon as I select the either the Shortage column or Unit Price column.

Please help!!!

[v-yulgu-msft]

Hi @TheITAnalyst13,

If [Shortage] column and [Unit Price] column exist in the same table as shown in above image, you can directly use this formula to create a calculated column.

Total Amount='TableName'[Shortage]*'TableName'[Unit Price]

If [Shortage] column and [Unit Price] column exist in two related tables, you can firstly combine them in the same table via LOOKUPVALUE or RELATED function based on table relationship. Then, you can still use above formula.

For more advice, please provide sample data of both two tables.

Regards,

Yuliana Gu

[TheITAnalyst13]

Hi @v-yulgu-msft,

There are 3 tables which are Related to reflect the Shortage Column.

This relationship allows to dynamically show the Shortage and Unit Prices based on month.

What I would like achieve is to have a calculation of the Total Amount (USD) based on the quantity in Shortage Column and Unit Price column.

Overdeployment Table

[Anonymous]

Since the storage column is from another table, try to use RELATED function and see if it works

Total Amount='TableName'[Unit Price]*RELATED('TableName'[Storage])