Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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!
Hi @Mavrick786
Check out the following link and see if it helps.
User | Count |
---|---|
77 | |
76 | |
44 | |
30 | |
26 |
User | Count |
---|---|
97 | |
91 | |
52 | |
47 | |
46 |