Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Mavrick786
New Member

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:

Mavrick786_0-1695746780204.png

 


Sample Data:-

 

DateToysGradeInventory
9/26/2023CarsA125
9/26/2023CarsB233
9/26/2023CarsC320
9/26/2023BikesA1100
9/26/2023BikesC3125
9/26/2023BikesD479
9/26/2023TrucksC315
9/26/2023TrucksD420
9/26/2023TrucksE537
10/3/2023CarsA138
10/3/2023CarsB246
10/3/2023CarsC333
10/3/2023BikesA1113
10/3/2023BikesC3138
10/3/2023BikesD492
10/3/2023TrucksC328
10/3/2023TrucksD433
10/3/2023TrucksE550
10/7/2023CarsA151
10/7/2023CarsB259
10/7/2023CarsC346
10/7/2023BikesA1126
10/7/2023BikesC3151
10/7/2023BikesD4105
10/7/2023TrucksC341
10/7/2023TrucksD446
10/7/2023TrucksE563


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 



1 ACCEPTED SOLUTION
danextian
Super User
Super User

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 )

 

 

danextian_0-1695802713530.png

danextian_1-1695802730933.png

Please see attached pbix for details

 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

3 REPLIES 3
danextian
Super User
Super User

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 )

 

 

danextian_0-1695802713530.png

danextian_1-1695802730933.png

Please see attached pbix for details

 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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
Super User
Super User

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...

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.