Forum Discussion
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
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 Amitchandak and thank you for the prompt response.
Agreed with you re point 1 and 2 - fairly easy.
Your solution works for point 3 too - thanks.
However, I had a filter on the Product column that I forgot to mention in my initial scenario.
Let's say I have Client 3 added to the scenario with Product 1 and 4. However, Product 4 should be excluded entirely - meaning that the "Total amount spent by client who bought selected product" for this client 3 would be £30 and never £60 - hope that makes sense.
What part of the measure should be amended to get the right result?
Thanks again!
R
Client ID | Product | Amount
Client 1 | Product 1 | £10
Client 1 | Product 2 | £30
Client 1 | Product 3 | £20
Client 2 | Product 1 | £25
Client 2 | Product 3 | £35
Client 3 | Product 1 | £30
Client 3 | Product 4 | £30
