Multiplying two columns from two different tables (direct query + local file)

[biotechanalyst]

Hello,

Requesting some help on what seems to be a straight forward function in power bi. Im trying to integrate C02 reporting emission factors (local file) and multiply by vendor invoice spend (direct query).

Ideally, I want to multiply the 'Factor' field in Cat_NAICS table by the 'Net amount USD' field in Vend Spend Sum table.

Table 1 'vendor spend summary' = Direct feed live dataset coming from Azure 

Table 2 'Cat_to_NAICS' = local excel file

Connected *many to one* by proc category

I can get the relationship correct and display the values in the same table. But i am having trouble multiplying the factor across to the related spend.

Ive treid writting a dax measure but i dont understand how the field isnt related when there is clearly a relationship connected as i wouldnt be able to bring these fields into the same table.

[Anonymous]

Hi, @biotechanalyst 

Based on your description and screenshot information, I tried to create two tables, and set the corresponding relationships according to your screenshots as follows:

When I tried to use the following DAX expression, it didn't reproduce the problem you were experiencing:

Measure = SUMX('Attendee Report','Attendee Report'[Actual]*RELATED('Registration Report'[Number]))

It also works fine in calculated columns:

It would be helpful if you could provide a sample PBIX file that doesn't contain private data and contains the current errors.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Start small, via SUMMARIZECOLUMNS.  Collect your measures via ADDCOLUMNS.  Only bring out the SUMX guns for the last step.

[biotechanalyst]

Can you elaborate more?

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...