Forum Discussion
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 have two sources of Power BI dataset and factor% multipliers comes from an online excel file.
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.
4 Replies
- Rupak_bi
Super User
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.
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.
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.
