cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lava_
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. 

 

lava__0-1652212908646.png

 

 

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 =
ADDCOLUMNS(
SUMMARIZE(
'Vendor Invoices_Master',
'Vendor Invoices_Master'[Vendor Category]),
"_NumVendors", NumVendors,
"_SumInvoice", SumInvoice,
"_Average", TotalAvg
)
VAR Result =
AVERAGEX(_Table, TotalAvg)
RETURN
Result
 
 
 
3 REPLIES 3
v-cazheng-msft
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 =
    ADDCOLUMNS (
        SUMMARIZE (
            'Vendor Invoices_Master',
            'Vendor Invoices_Master'[Vendor Category]
        ),
        "_NumVendors", NumVendors,
        "_SumInvoice", SumInvoice,
        "_Average", TotalAvg
    )
VAR Result =
    AVERAGEX ( _Table, TotalAvg )
RETURN
    Result

 

Then, the result looks like this.

vcazhengmsft_0-1653981619626.png

 

vcazhengmsft_1-1653981619627.png

 

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

amitchandak
Super User
Super User

@lava_ , You have try like for you measure

 

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

 

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

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:

lava__0-1652285406359.png

 

Incorrect Averages Once Vendor is Selected:

lava__2-1652285852299.png

 

Helpful resources

Announcements
Carousel_PBI_Wave1

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