Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating multiple column values using a row value

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.  
Need Help
Tips and Tricks
v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago
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)))))))))))))