Struggling to get summary number when using table expression

[mike_asplin]

Again I'm sure this is trivial for people in the know!!

I have a table of 'Providers' with the company name [Providers Name] each of which has multiple sites [Name]. Each site sites in an LA Name that has a ranking. I want to score the Provider based on a combination of the scores of the individual sites

I've got this far, but need the total of 3.75 to be on every row again every [Name] not the individual site scores.  I've spend hours trying everything I can think of, but becuase i'm using a talbe expression struggling to make an difference.   The [AVG LA Rank] is a measure because it changes with slicers. 

ProvTable gets the current LA Rank and adds it as a column to the list of provider sites, ProvWT adds a column for the weighting calculation. Last step is just to add up the [PWT] for each provider and display it against every site. Essentially going to use this to rank providers by how attractive they are to buy.  Just cant work it out so any assitance appreciated.

Mike

Tgt Operator Input = 
VAR
ProvValue=SELECTEDVALUE(Providers[Provider Name])
VAR
ProvTable=CALCULATETABLE(ADDCOLUMNS(Providers,
                         "ProvRank",ROUND(CALCULATE([AVG LA Rank],FILTER(MSOA_Mapping,MSOA_Mapping[LA Name]=Providers[LA Name])),1)))
VAR
ProvWT=CALCULATETABLE(ADDCOLUMNS(ProvTable,                         
                         "PWT", IF([ProvRank]<=2, 1, IF([ProvRank]<=3,0.5, IF([ProvRank]<=4,0.25)))))
RETURN
SUMX(FILTER(ProvWT,Providers[Provider Name]=ProvValue),[PWT])

[danextian]

Hi @mike_asplin 

Try this after RETURN

CALCULATE(
    SUMX(
        FILTER(
            ProvWT,
            Providers[Provider Name] = ProvValue
        ),
        [PWT]
    ),
    ALLSELECTED()
)

Or create another measure:

CALCULATE ( [Tgt Operator Input], ALLSELECTED () )

[mike_asplin]

First version doesnt work

but second version works if only 1 provider, as soon as I add 2 it returns null

.

Seems creating a second measure may be way to go, but i tried doing various optios and none worked.  

The model is here last tab https://www.dropbox.com/scl/fi/89elgxmbyqjocqdvvdof2/Trial-Provider-2011-Basis-Updated-May25-V4-All....

Thanks

[mike_asplin]

I have tried 20 different versions of the second measure that to my mind should work, but dont. I either just get the input number repeated or with this got a totla repeated, but not for each provider (shoudl be 4 and 3.75 ). I've tried adding a column to a table summarised by provider, ALLSELECTED (name), removefilters (name). I cant believe its this difficult. Issue with being an amateur and not really understanding what is going on under the bonnet!!! 

TGT Op Output = CALCULATE(SUMX(VALUES(Providers[Provider Name]),[Tgt Operator Input]),ALLSELECTED())

[burakkaragoz]

Hi @mike_asplin ,

Totally get what you're trying to do — and you're actually really close.

The issue is that your measure is returning the correct total per provider, but only for the row where SELECTEDVALUE(Providers[Provider Name]) returns a single value. When you're in a row context (like in a table visual), that works per row — but you want the same total repeated across all rows for that provider.

Here’s a quick fix: instead of using SELECTEDVALUE, try wrapping your logic in a CALCULATE with REMOVEFILTERS to ignore the row-level context:

Tgt Operator Input = 
CALCULATE(
    SUMX(
        ProvWT,
        ProvWT[PWT]
    ),
    REMOVEFILTERS(Providers[Name])
)

This way, the measure will always calculate the total PWT per provider, regardless of the individual site rows being shown.

If you want to make sure it still respects slicers (like Provider Name or LA Name), you can adjust the REMOVEFILTERS to only clear the site-level context:

Tgt Operator Input = 
CALCULATE(
    SUMX(
        ProvWT,
        ProvWT[PWT]
    ),
    REMOVEFILTERS(Providers[Name]) // clears only the site-level filter
)

Let me know if that works — or if you want to rank providers based on this total, we can build on top of this.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

Translation and text editing supported by AI

[mike_asplin]

Sorry doesnt work. You cant actually put this because it says cant find table. I dont think removefilters works on a table expression?

ProvWT[PWT]

[burakkaragoz]

@mike_asplin ,

Thanks for the follow-up. You're right to question that — REMOVEFILTERS doesn't work directly on a variable-based table expression like Prowf, since it's not part of the model schema. It only works on actual columns from your data model.

If you're trying to calculate the total PWT per provider and repeat it across all rows, one way is to calculate the total in a separate measure, and then bring it back using a LOOKUP or by restructuring the logic a bit.

Here’s a quick example of how you might approach it:

Total PWT by Provider =
CALCULATE(
    SUMX(ProvW, ProvW[PWT]),
    ALLEXCEPT(Providers, Providers[Provider Name])
)

This will give you the total PWT per provider, ignoring the site-level filter but keeping the provider context. You can then use this measure in your visuals and it should repeat across all rows for the same provider.

Let me know if you're still hitting issues — happy to help further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

---

Translation and text formatting supported by AI

[mike_asplin]

ah worked it out similar idea as need to use the actual table not the table expression

TGT Op Output = CALCULATE(
                        SUMX(Providers,[Tgt Operator Input]),
                        ALLEXCEPT(Providers, Providers[Provider Name])
                        )

I never understand why you cant do this within one measure and have to create 2 measures?  This seems to be a logical combination of the 2 measures, but it doesn't work (produces quite an odd answer)

Tgt Operator Input = 
VAR
ProvValue=SELECTEDVALUE(Providers[Provider Name])
VAR
ProvTable=CALCULATETABLE(ADDCOLUMNS(Providers,
                         "ProvRank",ROUND(CALCULATE([AVG LA Rank],FILTER(MSOA_Mapping,MSOA_Mapping[LA Name]=Providers[LA Name])),1)))
VAR
ProvWT=CALCULATETABLE(ADDCOLUMNS(ProvTable,                         
                         "PWT", IF([ProvRank]<=2, 1, IF([ProvRank]<=3,0.5, IF([ProvRank]<=4,0.25)))))
VAR
TOI=SUMX(FILTER(ProvWT,Providers[Provider Name]=ProvValue),[PWT])
RETURN
CALCULATE(
                        SUMX(Providers,TOI),
                        ALLEXCEPT(Providers, Providers[Provider Name])
                        )

Really appreciate your help as was driving me nuts

[mike_asplin]

Ah **bleep** it isnt the solution. I got the same number on every row, but its the wrong number!!!! it just happened to be right for the one I was looking at. 

so using this

Tgt Operator = CALCULATE(
                        SUMX(Providers,[Tgt Operator Input]),
                        ALLEXCEPT(Providers, Providers[Provider Name])
                        )

I get this. so Velocity was right, but others are wrong? Doesnt seem a difficult calculation

[mike_asplin · Accepted solution]

Tgt Operator = CALCULATE(
                        SUMX(SUMMARIZE(Providers,Providers[Provider Name]),[Tgt Operator Input]),
                        ALLEXCEPT(Providers, Providers[Provider Name])
                        )

This was the fix. No idea why adding the summarize was required!!!

[mike_asplin]

Wrong agian. Worked all except one Synexus. Makes no sense

[mike_asplin]

Found it. Synexus had a site in wales that is outside my demographic model so was retruning blank() for the LA rank = 1 for input so adding an extra 1 but not showing the site as not part of the hierachy.  Think its right now