Forum Discussion
Calculate sum for common items under multiple selected categories & slicers also show same behaviour
Hi,
I have a table with inventory data for toys with certain grades. Below you will find a small sample of the same.
There are to be two slicers on the page. Toys and Grade. For any two or more selected toy categories, the second slicer for Grade should filter out to show only common items between them. For e.g if a user selects Cars and Bikes, then Grade should only show A1 and C3 as options. The sum of inventory should also be the total of these common grades only.
Example:
Sample Data:-
| Date | Toys | Grade | Inventory |
| 9/26/2023 | Cars | A1 | 25 |
| 9/26/2023 | Cars | B2 | 33 |
| 9/26/2023 | Cars | C3 | 20 |
| 9/26/2023 | Bikes | A1 | 100 |
| 9/26/2023 | Bikes | C3 | 125 |
| 9/26/2023 | Bikes | D4 | 79 |
| 9/26/2023 | Trucks | C3 | 15 |
| 9/26/2023 | Trucks | D4 | 20 |
| 9/26/2023 | Trucks | E5 | 37 |
| 10/3/2023 | Cars | A1 | 38 |
| 10/3/2023 | Cars | B2 | 46 |
| 10/3/2023 | Cars | C3 | 33 |
| 10/3/2023 | Bikes | A1 | 113 |
| 10/3/2023 | Bikes | C3 | 138 |
| 10/3/2023 | Bikes | D4 | 92 |
| 10/3/2023 | Trucks | C3 | 28 |
| 10/3/2023 | Trucks | D4 | 33 |
| 10/3/2023 | Trucks | E5 | 50 |
| 10/7/2023 | Cars | A1 | 51 |
| 10/7/2023 | Cars | B2 | 59 |
| 10/7/2023 | Cars | C3 | 46 |
| 10/7/2023 | Bikes | A1 | 126 |
| 10/7/2023 | Bikes | C3 | 151 |
| 10/7/2023 | Bikes | D4 | 105 |
| 10/7/2023 | Trucks | C3 | 41 |
| 10/7/2023 | Trucks | D4 | 46 |
| 10/7/2023 | Trucks | E5 | 63 |
I've tried a few ways but not having any success. Any help would be greatly appreciated. Thanks!
parry2k amitchandak Greg_Deckler technolog Idrissshatila Ritaf1983 danextian Ahmedx
Hi Mavrick786 ,
Try these measures
Toy Count = VAR SelectedToys= ALLSELECTED ( 'Table'[Toys] ) RETURN CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Toys] ) ), REMOVEFILTERS (), VALUES ( 'Table'[Grade] ), SelectedToys )Inventory Sum = VAR __DISTINCT_TOY_COUNT = CALCULATE ( DISTINCTCOUNT ( 'Table'[Toys] ), ALLSELECTED ( 'Table' ) ) VAR __GRADE_TABLE = FILTER ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Grade] ), "@Toy Count", [Toy Count] ), [@Toy Count] = __DISTINCT_TOY_COUNT ) VAR __GRADE_COLUMN = SELECTCOLUMNS ( __GRADE_TABLE, "@Grade", [Grade] ) RETURN CALCULATE ( SUM ( 'Table'[Inventory] ), 'Table'[Grade] IN __GRADE_COLUMN )Please see attached pbix for details
3 Replies
Hi Mavrick786 ,
Try these measures
Toy Count = VAR SelectedToys= ALLSELECTED ( 'Table'[Toys] ) RETURN CALCULATE ( COUNTROWS ( VALUES ( 'Table'[Toys] ) ), REMOVEFILTERS (), VALUES ( 'Table'[Grade] ), SelectedToys )Inventory Sum = VAR __DISTINCT_TOY_COUNT = CALCULATE ( DISTINCTCOUNT ( 'Table'[Toys] ), ALLSELECTED ( 'Table' ) ) VAR __GRADE_TABLE = FILTER ( ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Grade] ), "@Toy Count", [Toy Count] ), [@Toy Count] = __DISTINCT_TOY_COUNT ) VAR __GRADE_COLUMN = SELECTCOLUMNS ( __GRADE_TABLE, "@Grade", [Grade] ) RETURN CALCULATE ( SUM ( 'Table'[Inventory] ), 'Table'[Grade] IN __GRADE_COLUMN )Please see attached pbix for details
Thank you so much danextian . Can you help me with a little extra insight on this.
I have another column called product and wanted to visualise it on a hierarchial slicer like Grade>Product.
I tried to modify your measure Inventory Sum to include this product column in the summarize function and then in select columns. But after changing the filter context to include the Product filter it didn't work.
Can you please help me with that. Will really appreciate it. Thanks!
