Missing filter option after grouping

[lena10]

Hello everyone,

I have a table with several deliveries from different suppliers and information on how punctual the delivery was. I would now like to calculate the average per supplier for on-time delivery and then group this into categories (e.g. ‘Good’ for averages greater than 50). The report view should then show me how many suppliers have their average in which category.
I have already solved the problem by duplicating the table in PowerQuery, grouping it and then adding a new column with the category. Unfortunately, the resulting pie chart in which the suppliers are counted can of course no longer be filtered (e.g. by date of delivery).
The filter option is important, however, as the average changes depending on the time period selected in the report view.
Does anyone have a solution for this?

Thank you very much!

[v-jianpeng-msft]

Hi, @lena10 

If you need to dynamically change based on your slicer selection, you'll need to create a measure. I created the following sample data:

First, we need to create an On-Time Delivery Rate (OTD) metric for each supplier:

OTD Rate (%) = 
VAR _Ontime_deliever = SELECTEDVALUE(SupplierDelivery[On-Time Deliveries])
VAR _total_order = SELECTEDVALUE(SupplierDelivery[Total Orders])
RETURN
DIVIDE(_Ontime_deliever, _total_order) * 100

After that we need to create a measure that groups suppliers:

Supplier Category = IF(ISINSCOPE(SupplierDelivery[Supplier]),
SWITCH(
    TRUE(),
    [OTD Rate (%)] > 90, "Excellent",
    [OTD Rate (%)] >= 70 && [OTD Rate (%)] <= 90, "Good",
    [OTD Rate (%)] >= 50 && [OTD Rate (%)] < 70, "Fair",
    "Needs Improvement"
)
)

Put these measures in the table visual:

Metrics dynamically calculate groupings based on your slicer selections. 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.

[lena10]

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.

[v-jianpeng-msft · Accepted solution]

Hi, @lena10 

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.