Calculate sum for common items under multiple selected categories & slicers also show same behaviour

[Mavrick786]

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 

[danextian · Accepted solution]

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

[Mavrick786]

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!

[gmsamborn]

Hi @Mavrick786 

Check out the following link and see if it helps.

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/td-p/3918...