Need help for formula (sum of many selected item in slicer)

[Lesliekeziah]

Hi All formula experts,

Can you please help me. I am trying to workout the formula to calculate the sum of all items if two or more items were selected in the slicer. Please refer below to my existing formula and the visualization.

Result Wanted: If Selected Items are D & E, Data Will Show

D 21

E 2

F 24

G 14

and so on and so forth...

Thank you all for your help 🙂

formula:

=CALCULATE (SUM(Orders[Quantity2]), CALCULATETABLE(SUMMARIZE(Orders,Orders[Document Number/ID]), ALL(Orders[Item]), USERELATIONSHIP(Orders[Item],'Filter Product'[Item])))

[v-yanimei-msft]

Hi @Lesliekeziah , 

From your issue, I guess you may need the result as following steps.

Please follow these steps:

1. Two test tables are in following  two pictures.
2. Right-click Table, select New Measure and input:

Measure = CALCULATE(
    SUM('Table'[num]),
    FILTER(
        'Table',
        'Table'[item2] IN VALUES('Filter Product'[Item])
    )
)
​

• If you select B&C, the result will be shown as the following picture.

Best Regards,

Caroline Mei
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lesliekeziah]

Hi

Thank you so much for your input.

Not sure if this will work. I have 200 products with 2k plus transactions. What if in a transaction I won't be able to put the 'dummy' item, I don't think it will capture the quantity. Thank you again

[some_bih]

Hi @Lesliekeziah 

Check your column selections and measure applied, see picture it could be reason for wrong amounts.

[Lesliekeziah]

Hi there, 

the quantity is correct. what I am after is that when I select D and E in the slicer, it will some up everything for all the items (not separate ). For Instance, if I select D and E, item F will have a quantity of 24 (12 from Item D and 12 from Item E). Hopefully it makes sense. THanks