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 for categories and i need to show the sum of amount

for Category A and B, I need to use amount 1
for Category C, I need to use amount 2
For Category D, I need to use amount 3

i tried using switch but if i select all categories, the value will be blank, if i select any 2 or more , i get blank , 

how can i make it sum based on each selection? so lets say i selected A and C, i need the sumx of Total amount for A + Total Amount for C

Best Regards

  • 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

1 Reply

  • 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