Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

How to keep filter on category but remove it on # of units within category?

Hi there,


I have a report that analyzes how much we've spent on third-party vendors. We have 26 different vendor categories that the vendors can fall under. I'm having issues with displaying the average category cost over a single year on a line chart on a drill through page. 


When a user drills in on a single vendor, the line chart is supposed to show the average amount spent within that selected vendor's category over a singler year (so the average would include total spent within that category divided by the number of vendors within that category). At the bottom is the formula used to determine the average category total, but this becomes challenging when a user drills in on a vendor. When they drill in, it filters on the vendor and then on the vendor's category. So the average category total runs as if there's only one vendor in that category, which is obviously incorrect.


Question: How can I keep the filter on the vendor category, but remove the filter on the number of vendors in that category? For example, looking at the screenshot below, if I drill in on Vendor 1, how can I keep the filter on the category "Trading System" and still have the number of vendors be 7? Would I apply ALL or KEEP/REMOVEFILTERS somewhere in the formula below? Again, note that this information would be displayed in a line chart, not in a matrix. 





Category Average =
VAR NumVendors = COUNTROWS(DISTINCT('Vendor Invoices_Master'[Vendor Reporting Name]))
VAR SumInvoice = SUM('Vendor Invoices_Master'[Invoice Amount])
VAR TotalAvg = DIVIDE(SumInvoice, NumVendors)
VAR _Table =
'Vendor Invoices_Master',
'Vendor Invoices_Master'[Vendor Category]),
"_NumVendors", NumVendors,
"_SumInvoice", SumInvoice,
"_Average", TotalAvg
VAR Result =
AVERAGEX(_Table, TotalAvg)
Community Support
Community Support

Hi @lava_,


Maybe you can try this Measure.

Category Average =
VAR NumVendors =
   //add all function to remove the filters on Vendor Invoices Master table
   CALCULATE( COUNTROWS ( DISTINCT ( 'Vendor Invoices_Master'[Vendor Reporting Name] ) ),ALL('Vendor Invoices_Master'))
VAR SumInvoice =
    SUM ( 'Vendor Invoices_Master'[Invoice Amount] )
VAR TotalAvg =
    DIVIDE ( SumInvoice, NumVendors )
VAR _Table =
        SUMMARIZE (
            'Vendor Invoices_Master',
            'Vendor Invoices_Master'[Vendor Category]
        "_NumVendors", NumVendors,
        "_SumInvoice", SumInvoice,
        "_Average", TotalAvg
VAR Result =
    AVERAGEX ( _Table, TotalAvg )


Then, the result looks like this.





All function will return all the rows in the table, if use it in the filters part of Calculate function, then expression part of Calculate function will evaluate based on the entire tables. For more details, you may refer to ALL function (DAX) - DAX | Microsoft Docs.


If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!


Best Regards,

Community Support Team _ Caiyun

Super User
Super User

@lava_ , You have try like for you measure


if(isinscope(Table[Vendor]),calculate([Measure], REMOVEFILTERS (Table[Vendor])), [Measure])

Thank you for your help @amitchandak . This unfortunately is still not removing the vendor selection filter, so the average is still calculating as if there's only one vendor in the category. I also tested it out in a table visual and the averages are correct without any filters applied, but once a vendor is selected in a separate slicer to filter the table, the averages become incorrect. 


Correct Averages:



Incorrect Averages Once Vendor is Selected:



Helpful resources


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors