Forum Discussion
Missing filter option after grouping
Hi, Anonymous
First, you'll need to create a new table with the following:
Use the following DAX expression to create a category measure:
category = VAR _table = SUMMARIZE ( 'Table', 'Table'[Supplier], 'Table'[Order Number], "On time dilivery", ( SUM ( 'Table'[OTD Rate] ) / 100 ) * 'Table'[Order Number] ) VAR _table2 = ADDCOLUMNS ( SUMMARIZE ( _table, 'Table'[Supplier], "average", VAR aa = SUMX ( FILTER ( _table, 'Table'[Supplier] = EARLIER ( 'Table'[Supplier] ) ), [On time dilivery] ) VAR _total = SUMX ( FILTER ( _table, 'Table'[Supplier] = EARLIER ( 'Table'[Supplier] ) ), 'Table'[Order Number] ) VAR _average = aa / _total RETURN CEILING ( _average * 100, 1 ) ), "category", IF ( [average] >= 70, "Good", "Critical" ) ) RETURN MAXX ( FILTER ( _table2, 'Table'[Supplier] = SELECTEDVALUE ( 'Table'[Supplier] ) ), [category] )Next, create a count measure:
Count1 = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[Supplier], "category", [category] ), [category] = SELECTEDVALUE ( 'Table 3'[category] ) ) )Use the category and count1 metrics as follows:
Changing the date selected by the slicer counts dynamically:
I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your help!
My data looks more like this, just with even more columns with other information.
At the end I would like to have a table or a pie chart showing the number of suppliers that are in the categories with their average. E.g. all with an average >70 are ‘Good’ and all others are ‘Critical’:
Normally I would group the data in PowerQuery, then make a new column with the category and then count how many suppliers with this category there are. But then unfortunately my visuals are no longer dynamic and the filters don't work.
I hope this explains the problem a little better.
Hi, Anonymous
First, you'll need to create a new table with the following:
Use the following DAX expression to create a category measure:
category =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Supplier],
'Table'[Order Number],
"On time dilivery",
( SUM ( 'Table'[OTD Rate] ) / 100 ) * 'Table'[Order Number]
)
VAR _table2 =
ADDCOLUMNS (
SUMMARIZE (
_table,
'Table'[Supplier],
"average",
VAR aa =
SUMX (
FILTER ( _table, 'Table'[Supplier] = EARLIER ( 'Table'[Supplier] ) ),
[On time dilivery]
)
VAR _total =
SUMX (
FILTER ( _table, 'Table'[Supplier] = EARLIER ( 'Table'[Supplier] ) ),
'Table'[Order Number]
)
VAR _average = aa / _total
RETURN
CEILING ( _average * 100, 1 )
),
"category", IF ( [average] >= 70, "Good", "Critical" )
)
RETURN
MAXX (
FILTER ( _table2, 'Table'[Supplier] = SELECTEDVALUE ( 'Table'[Supplier] ) ),
[category]
)
Next, create a count measure:
Count1 =
COUNTROWS (
FILTER (
SUMMARIZE ( 'Table', 'Table'[Supplier], "category", [category] ),
[category] = SELECTEDVALUE ( 'Table 3'[category] )
)
)
Use the category and count1 metrics as follows:
Changing the date selected by the slicer counts dynamically:
I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.