Forum Discussion
Helper VStruggling to get summary number when using table expression
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])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!!!
11 Replies
- burakkaragoz
Super User
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_asplinSorry 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
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
- 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.pbix?rlkey=xnhpk9tsftvkg65qpza3w71ww&dl=0
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())
