Forum Discussion

tusharpk99's avatar
tusharpk99
New Member
2 years ago
Solved

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

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 h...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Rupak_bi
Icon for Super User rankSuper User
2 years ago

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's avatar
tusharpk99
New Member
2 years ago

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's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    2 years ago

    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.