topic Re: How to keep filter on category but remove it on # of units within category? in DAX Commands and Tips

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

lava_ — Tue, 10 May 2022 22:12:18 GMT

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 =

ADDCOLUMNS(

SUMMARIZE(

'Vendor Invoices_Master',

'Vendor Invoices_Master'[Vendor Category]),

"_NumVendors", NumVendors,

"_SumInvoice", SumInvoice,

"_Average", TotalAvg

)

VAR Result =

AVERAGEX(_Table, TotalAvg)

RETURN

Result

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

amitchandak — Wed, 11 May 2022 06:17:22 GMT

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/

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

lava_ — Wed, 11 May 2022 16:18:49 GMT

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:

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

v-cazheng-msft — Tue, 31 May 2022 07:21:13 GMT

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.

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