Help getting the right total in matrix table

rmsl , I think first two should be simple

# Client that bought selected product [Expected Result = 1]

Count(Table[Client])

Amount spent in selected product [Expected Result = £30]

Sum(Table[Amount])

Total amount spent by client who bought selected product [Expected Result = £60]

Measure  =

var _tab = summarize(allselected(Table), Table[Client])

return

Calculate(sum(Table), Filter(all(Table), Table[Client]  in _tab) )

Hi  rmsl ,

You can create two slicers.

A slicer for normal selection of Products.

Another slicer to exclude the selected Product.

Here are the steps you can follow：

1. Create calculated table.

optional =
DISTINCT('Table'[Product])

not optional =
DISTINCT('optional'[Product])

2. Create measure.

Client that bought selected product =
var _select=
SELECTEDVALUE('optional'[Product])
return
CALCULATE(COUNT('Table'[Amount]),FILTER(ALL('Table'),'Table'[Product]=_select))

Amount spent in selected product =
var _select=SELECTEDVALUE('optional'[Product])
return
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(ALL('Table'),
    'Table'[Product]=_select))

Total amount spent by client who bought selected product =
var _select=SELECTEDVALUE('optional'[Product])
var _noselect=SELECTEDVALUE('not optional'[Product])
var _selectcolumn=
SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Product]=_select),"clinet",[Client ID])
return
IF(
    HASONEVALUE('not optional'[Product]),
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Client ID] in _selectcolumn&&'Table'[Product]<>_noselect)),
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Client ID] in _selectcolumn))
)

3. Result:

Treat [Product] of optional table as slicer 1

Treat [Product] of not optional table as slicer 2.

When slicer 2 is not selected, it is calculated normally and displays 180.

When slicer 2 selects a value, such as Product4, it will not contain the value of Product4, and it will be displayed as 150

If you need pbix, please click here.

Best Regards,

Liu Yang

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