Stuck due to columns total while rows correct - two data sources

[tusharpk99]

Dear All. I am stuck with a unique

I need to multiply countries revenue with a % factor while the rows are correct, column total incorrect.

So i used DAX of SUMX but it has error of 1M + rows.

I have two sources of Power BI dataset and factor% multipliers comes from an online excel file.

[Anonymous · Accepted solution]

Hi @tusharpk99 ,

Below is my table:

Below is my table1:

Merge the two tables into a single table(Left Outer):

The following DAX might work for you:

Measure = 
  SUMX(
    Merge1,
    Merge1[Revenue] * Merge1[Table1.Fac]
  )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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

[Rupak_bi]

Hi,

If you do it in column, It will work. bring the % factors in your dataset based on the relationship and then create a calculated column to multiply.

[tusharpk99]

how to bring % factors in the dataset from the excel file ? i tried but it shows the error.

Column cannot be pushed to the remote data source and cannot be used in this scenario

[Rupak_bi]

you may make a relationship based on country names to connect both the tables and then bring the % factors in power bi dataset query table using lookup value or related dax function in a new calculated column.