topic Re: Average of categories over vendor in DAX Commands and Tips

Average of categories over vendor

torishriver — Wed, 11 Feb 2026 18:21:46 GMT

Hello,

I'm trying to calculate the category average of vendors, but view the specific vendor. I need to make my formula calculate the score of the category that the selected vendor is in. It currently is showing the same average value for all 4 categories, when they should be different. What do I need to change in my formula?

%_VendorScore_Avg_Category =

VAR brand = 0.14

VAR eob = 0.13

VAR finance = 0.17

VAR service = 0.18

VAR supplychain = 0.14

VAR technology = 0.09

VAR opportunity = 0.15

VAR SelectedCategory =

SELECTEDVALUE ( 'Vendor Scorecard'[Category] )

RETURN

CALCULATE (

([#_BrandScore_Avg] * brand)

+ ([#_EaseofBusiness_Avg] * eob)

+ ([#_Finance_Avg] * finance)

+ ([#_Service_Avg] * service)

+ ([#_SupplyChain_Avg] * supplychain)

+ ([#_Technology_Avg] * technology)

+ ([#_Opportunity_Avg] * opportunity),

REMOVEFILTERS ('Vendor Scorecard'[Vendor Name]),

'Vendor Scorecard'[Category] = SelectedCategory

)

Re: Average of categories over vendor

pcoley — Thu, 12 Feb 2026 02:24:26 GMT

The main issue in your current measure is how the filter context and SELECTEDVALUE interact when a single vendor (or multiple vendors) is selected in a table/matrix visual.

When you put [Vendor Name] on rows and this measure in values:

SELECTEDVALUE ( 'Vendor Scorecard'[Category] ) usually returns BLANK (not the category), because the filter context has multiple rows from the same category → SELECTEDVALUE returns blank when >1 distinct value exists.
When it is blank, your filter 'Vendor Scorecard'[Category] = SelectedCategory becomes [Category] = BLANK(), which matches nothing → you get the grand total average across all vendors/categories (that's why you see the same number everywhere).

Recommended Fix (Most Common & Clean Pattern)

Remove SELECTEDVALUE and let the existing row context from the visual (or slicer) set the category automatically.

You only need to remove the vendor filter — keep the category filter coming from the visual.

dax

%_VendorScore_Avg_Category =
VAR brand       = 0.14
VAR eob         = 0.13
VAR finance     = 0.17
VAR service     = 0.18
VAR supplychain = 0.14
VAR technology  = 0.09
VAR opportunity = 0.15

RETURN
    CALCULATE (
        ([#_BrandScore_Avg] * brand)
        + ([#_EaseofBusiness_Avg] * eob)
        + ([#_Finance_Avg] * finance)
        + ([#_Service_Avg] * service)
        + ([#_SupplyChain_Avg] * supplychain)
        + ([#_Technology_Avg] * technology)
        + ([#_Opportunity_Avg] * opportunity),

        REMOVEFILTERS ( 'Vendor Scorecard'[Vendor Name] )
        -- optionally: REMOVEFILTERS ( 'Vendor Scorecard'[Vendor Name], other columns you want to ignore )
    )

Why this usually works best

When the visual has [Category] on rows (or in a slicer) → category filter stays → you get correct per-category weighted avg
When the visual has [Vendor Name] on rows → vendor filter is removed → calculation happens over all vendors in the same category as the current vendor row (because category filter from the data model / relationship / page / slicer remains)
When nothing filters category → you get the overall weighted average (grand total)

Alternative (Explicit — if the simple version doesn't work)

If you have a complex filter setup and the category context is being lost, you can capture the vendor's category first:

dax

%_VendorScore_Avg_Category =
VAR brand       = 0.14
VAR eob         = 0.13
VAR finance     = 0.17
VAR service     = 0.18
VAR supplychain = 0.14
VAR technology  = 0.09
VAR opportunity = 0.15

VAR CurrentVendorCategory =
    CALCULATE(
        SELECTEDVALUE ( 'Vendor Scorecard'[Category] ),
        REMOVEFILTERS ( 'Vendor Scorecard'[Vendor Name] )   // optional — usually not needed here
    )

RETURN
    IF(
        ISBLANK ( CurrentVendorCategory ),
        BLANK(),     // or return grand total if preferred
        CALCULATE (
            ([#_BrandScore_Avg] * brand)
            + ([#_EaseofBusiness_Avg] * eob)
            + ([#_Finance_Avg] * finance)
            + ([#_Service_Avg] * service)
            + ([#_SupplyChain_Avg] * supplychain)
            + ([#_Technology_Avg] * technology)
            + ([#_Opportunity_Avg] * opportunity),

            REMOVEFILTERS ( 'Vendor Scorecard'[Vendor Name] ),
            'Vendor Scorecard'[Category] = CurrentVendorCategory
        )
    )

But in 90% of typical vendor/category scorecard reports, the first (simpler) version is enough and cleaner.

Quick Test Sequence

Try the first (simpler) version — just remove the SELECTEDVALUE part and the category filter.
Put [Vendor Name] on rows + this measure → each vendor should now show its category's average.
If still wrong → check whether [Category] is on the visual, in a slicer, or coming from a relationship — and share more about your visual setup.

Re: Average of categories over vendor

FBergamaschi — Thu, 12 Feb 2026 11:50:58 GMT

Can you share the pbix via a could service or send it via mail in a private message? That would speed things up

Anyway, you need to iterate the categories to make your code work, so you need something like

%_VendorScore_Avg_Category =

VAR brand = 0.14

VAR eob = 0.13

VAR finance = 0.17

VAR service = 0.18

VAR supplychain = 0.14

VAR technology = 0.09

VAR opportunity = 0.15

RETURN
SUMX (
VALUES ('Vendor Scorecard'[Category] )),

CALCULATE (

([#_BrandScore_Avg] * brand)

+ ([#_EaseofBusiness_Avg] * eob)

+ ([#_Finance_Avg] * finance)

+ ([#_Service_Avg] * service)

+ ([#_SupplyChain_Avg] * supplychain)

+ ([#_Technology_Avg] * technology)

+ ([#_Opportunity_Avg] * opportunity),

REMOVEFILTERS ('Vendor Scorecard'[Vendor Name])

)

If I can get the pbix I can in case fix other nuances

If this helped, please consider giving kudos and mark as a solutionme in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Average of categories over vendor

v-achippa — Mon, 16 Feb 2026 12:05:49 GMT

Hi torishriver,

Thank you for reaching out to Microsoft Fabric Community.

Thank you pcoley and FBergamaschi for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

Re: Average of categories over vendor

torishriver — Mon, 16 Feb 2026 13:44:00 GMT

Thank you for responding, Francesco. Unfortunately I cannot share the PBI dashboard with you as it has customer sensitive data. Your solution also didn't work for me, but I think it has to do with the way I built the measures that I'm referencing in this formula.

Re: Average of categories over vendor

torishriver — Mon, 16 Feb 2026 13:45:58 GMT

Thank you for responding. Unfortunately this solution also didn't work for me, but I think it has to do with the way I built the measures that I'm referencing in this formula. The category is not a filter on my page, my page is being filtered to a specific vendor via a slicer, but I want this value to be calculated across all vendors that have the same category as the one in the slicer. I don't know if that changes anything though.

Re: Average of categories over vendor

Praful_Potphode — Wed, 18 Feb 2026 17:58:17 GMT

Hi torishriver ,

Please try sample pbix and let me know.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

Re: Average of categories over vendor

v-achippa — Fri, 20 Feb 2026 11:41:49 GMT

Hi @torishriver,

Thank you Praful_Potphode for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa