Calculating multiple column values using a row value

[Anonymous]

See there're 2 tables Table 1 & Table 2.

I need to get the sum for row values of table 1 from columns of table 2.

[v-yanjiang-msft]

Hi @Anonymous ,

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.

[Anonymous]

Dear, v-kalyj-msft

I figured it out using the below formula, your solution is quite impressive as well but I don't need max values but the sum of all values in respective columns. The below formula worked for me (Type is renamed to Sum Column)

Total Sum = IF(Master[Sum Column] ="sales A",CALCULATE(SUMX('Store Report','Store Report'[sales A])),IF(Master[Sum Column]="sales B",CALCULATE(SUMX('Store Report','Store Report'[Sales B])),IF(Master[Sum Column]="Sales C",CALCULATE(SUMX('Store Report','Store Report'[Sales C])),IF(Master[Sum Column]="sales D",CALCULATE(SUMX('Store Report','Store Report'[sales D])),IF(Master[Sum Column]="Sales E",CALCULATE(SUMX('Store Report','Store Report'[Sales E])),IF(Master[Sum Column]="Sales F",CALCULATE(SUMX('Store Report','Store Report'[Sales F])),IF(Master[Sum Column]="Sales G",CALCULATE(SUMX('Store Report','Store Report'[Sales G])),IF(Master[Sum Column]="Sales H",CALCULATE(SUMX('Store Report','Store Report'[Sales H])),IF(Master[Sum Column]="Sales I",CALCULATE(SUMX('Store Report','Store Report'[Sales I])),IF(Master[Sum Column]="Sales J",CALCULATE(SUMX('Store Report','Store Report'[Sales J])),IF(Master[Sum Column]="Sales K",CALCULATE(SUMX('Store Report','Store Report'[Sales K])),IF(Master[Sum Column]="Sales L",CALCULATE(SUMX('Store Report','Store Report'[Sales L])),IF(Master[Sum Column]="Sales M",CALCULATE(SUMX('Store Report','Store Report'[Sales M])),0)))))))))))))

[Fowmy]

@Anonymous 

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

[Anonymous]

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.

[Fowmy]

@Anonymous 
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. 

Doing it dax is painful and will cause memory and performance issues:

However, You can try this approach with DAX  with UNION and SELECTCOLUMNS : https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

[Anonymous]

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