Sum of averages from a column

[Anonymous]

Hi All,

I have a dataset as below :

City	Total lenght in Kms
London	80
London	80
Paris	50
Paris	50
Paris	50
Paris	50

I have a card display where I need to show the average of lenght when a city is selected. Which I have acheived by averaging on a selected city. However, if two cities are selected it is adding up both the cities length and averaging on it. Ex : (50+80)/2 = 65

I need to perform an average on both cities seperately and add up the averages. 

Ex : 50 + 80 = 130. How do I achieve it?  

[Anonymous · Accepted solution]

usure why this is not working for me, the city and lenght are from calculated tables. I tried changing the lenght in mts to a measure, but still not working :

var single = CALCULATE(AVERAGEX(Table, VALUES(Table[city])),[AveTLeninKms])

var multi = SUMX(VALUES(Table[city]), single)

return multi

error :A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed

[samdthompson]

hello, try something like this:

SumAvg=

VAR single=CALCULATE(AVERAGEX(VALUES(Table1[City]),Table1[Distance]))

VAR multiple=SUMX(VALUES(Table1[City]),single)

RETURN

multiple

[Anonymous · Accepted solution]

usure why this is not working for me, the city and lenght are from calculated tables. I tried changing the lenght in mts to a measure, but still not working :

var single = CALCULATE(AVERAGEX(Table, VALUES(Table[city])),[AveTLeninKms])

var multi = SUMX(VALUES(Table[city]), single)

return multi

error :A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed

[samdthompson · Accepted solution]

not quite the same. try this:

VAR single=CALCULATE(AVERAGEX(VALUES('Table'[AveTLeninKms]),'Table'[AveTLeninKms]))

VAR multi=SUMX(VALUES('Table'[City]),single)

RETURN

multi

[Anonymous]

This works, thanks!

[samdthompson]

no worries. it was the 'Table' vs Table that was off