topic Re: Stuck due to columns total while rows correct - two data sources in DAX Commands and Tips

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

tusharpk99 — Sat, 04 Nov 2023 05:40:25 GMT

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.

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

Rupak_bi — Sat, 04 Nov 2023 08:04:17 GMT

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.

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

tusharpk99 — Sat, 04 Nov 2023 09:07:48 GMT

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

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

Anonymous — Mon, 06 Nov 2023 08:05:17 GMT

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.

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

Rupak_bi — Tue, 07 Nov 2023 16:41:43 GMT

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.