Calculating multiple column values using a row value

Question

See there're 2 tables Table 1 &amp; Table 2.&nbsp;I need to get the sum for row values of table 1 from columns of table 2.&nbsp;

anonymous · Answer

I have managed to do so using if sum &amp; calculate but the result is constant and would not change as per any applied slicer but thanks a lot for reaching out (Y)

fowmy · Answer

Anonymous&nbsp;First, you need unpivot our Table 2 so you will have three columns as you need in Power Query:https://www.youtube.com/watch?v=Vff2kRBM95o

fowmy · Answer

Anonymous&nbsp;I would still advise you to re-organize your data as you have a large amount. You have it done at the source level. You can request your data source owner to do it for you.&nbsp;Doing it dax is painful and will cause memory and performance issues:However, You can try this approach with DAX&nbsp; with UNION and SELECTCOLUMNS :&nbsp;https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

anonymous · Answer

I already tried unpivoting the columns but it would increase the loading time way more for the data is more then 100Mn rows. if i don't unpivot the loading time is 2 minutes approx but if i do the loading time jumps to 1hr.

v-yanjiang-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
According to your description, here's my solution.
1.Create relationship between the two tables.

2.Create a measure in the table1.
Total Sum = 
SWITCH (
    MAX ( 'Table1'[Type] ),
    "SalesA", MAX ( 'Table2'[SalesA] ),
    "SalesB", MAX ( 'Table2'[SalesB] ),
    "SalesC", MAX ( 'Table2'[SalesC] ),
    "SalesD", MAX ( 'Table2'[SalesD] ),
    "SalesE", MAX ( 'Table2'[SalesE] ),
    "SalesF", MAX ( 'Table2'[SalesF] )
)

3.Put Range of table2 , Type of table and the measure into the table visual, get the expected result.

I attach my sample below for reference.
Best Regards,Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Calculating multiple column values using a row value

6 Replies

Related content

TMDL - calculated column

Calculated column RANKX with multiple columns

Calculating Average over Multiple Columns

Direct Lake Model - Calculated Column/Tables

Using Visual Calculation on a Calculated Group with Multiple Calculated Items