Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dynamic Sum for different column based on each category

Hello everyone, i have a table that has the following columns: category, amount 1, amount 2, amount 3 in the category column, i have 4 categories lets say from A to D i added a slicer fo...
  • vicky_'s avatar
    vicky_
    2 years ago

    So I created a measure for each sum amount (i.e Sum Amt 1 = SUM(Table[Amt 1])... etc) for each of the columns. 
    Then i created this measure: 

    Sum Amount = 
var _a = SUMMARIZECOLUMNS('Table'[Category])
return SUMX( _a, 
    SWITCH([Category], 
    "A", [Sum Amt 1],
    "B", [Sum Amt 1],
    "C", [Sum Amt 2],
    "D", [Sum Amt 3],
    0
    )
)

    which gives the following result:

    Hope this helps

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

So I created a measure for each sum amount (i.e Sum Amt 1 = SUM(Table[Amt 1])... etc) for each of the columns. 
Then i created this measure: 

Sum Amount = 
var _a = SUMMARIZECOLUMNS('Table'[Category])
return SUMX( _a, 
    SWITCH([Category], 
    "A", [Sum Amt 1],
    "B", [Sum Amt 1],
    "C", [Sum Amt 2],
    "D", [Sum Amt 3],
    0
    )
)

which gives the following result:

Hope this helps