Multiply 2 columns in a visual table - one source is from import, the other from live dataset PBI

[justfaryal]

I want to multiply the volumes(from a live connected source) with the $benefit PI1(from a table) and the two tables have countries related (which is connected in modelling view). For context, each country has a different benefit factor which is why for example Belgium volume x 0.057 whereas Italy volume x 0.171. Can someone tell me how to do this? I have tried quick measures, lookup to create the $benefit factor column in the live dataset and other functions but nothing seems to work...(super new to PowerBI 😞 ) 

[Anonymous · Accepted solution]

Hi  @justfaryal ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _sum1=
SUMX(
    FILTER(ALL(PI1),
    'PI1'[Sold To Country]=MAX('Multiply'[Sold To Country])),[$Benefit PI1])
return
 MAX('Multiply'[Volume]) * _sum1

2. Result:

Best Regards,

Liu Yang

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