Forum Discussion
Help getting the right total in matrix table
Hi All,
I've been trying to work this out for a couple of days now and I did not encounter any similar issue on the community so decided to ask for help.
I have a table showing the following values;
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
On a Report, I have a slicer on Product and a Matrix Table. I wish to retrieve the following information in the Matrix Table:
Slicer Selection = Product 2
Matrix table:
# Client that bought selected product [Expected Result = 1]
Amount spent in selected product [Expected Result = £30]
Total amount spent by client who bought selected product [Expected Result = £60]
Slicer Selection = Product 1
Matrix table:
# Client that bought selected product [Expected Result = 2]
Amount spent in selected product [Expected Result = £35]
Total amount spent by client who bought selected product [Expected Result = £120]
The info that I am struggling to get is the "Total amount spent by client who bought selected product".
I have tried to ignore the filter/slicer for this particular measure but it would return £120 in all cases by doing so which isn't right.
Appreciate any help I can get from the community.
Thanks,
R
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
6 Replies
- amitchandak
Super User
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
Hi rmsl ,
Here are the steps you can follow：
1. Create calculated table.
Table 2 = DISTINCT('Table'[Product])2. Create measure.
Client that bought selected product = var _select= SELECTEDVALUE('Table 2'[Product]) return CALCULATE(COUNT('Table'[Amount]),FILTER(ALL('Table'),'Table'[Product]=_select))Amount spent in selected product = var _select=SELECTEDVALUE('Table 2'[Product]) return CALCULATE( SUM('Table'[Amount]), FILTER(ALL('Table'), 'Table'[Product]=_select))Total amount spent by client who bought selected product = var _select=SELECTEDVALUE('Table 2'[Product]) var _selectcolumn= SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Product]=_select),"clinet",[Client ID]) return CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Client ID] in _selectcolumn))3. Result:
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
Hi Anonymous ,
Thanks for looking into this issue.
Unfortunately, this isn't the result I expected.
I had a look into your pbix report and as you can see, I am excluding the product 4 from the page.
Meaning that I would want to see a total of 150 instead of 180 when selecting product 1 for example.
Does that make sense?
Best Regards,
R
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
At the end I have used amitchandak solution as it did not create additional tables.
Regarding the product excluded, I have simply created another Amount Column with £0 for Product 4 as follow;
I prefered this solution as Anonymous solution who created 2 tables but if you do not mind about the two additional tables, then Anonymous solution would work too.
Thanks again both for your help on this issue.
Best,
R
Client ID | Product | Amount | Amount (excl P4)
Client 1 | Product 1 | £10 | £10
Client 1 | Product 2 | £30 | £30
Client 1 | Product 3 | £20 | £20
Client 2 | Product 1 | £25 | £25
Client 2 | Product 3 | £35 | £35
Client 3 | Product 1 | £30 | £30
Client 3 | Product 4 | £30 | £0
