Average of averages within group

[frithjof_v]

Hi,

I am trying to make a measure to calculate the average per city. It shall be the average of the visible values in the "Average of amount" column per city.

As shown below:

I have just hardcoded the values in the rightmost column, to show what I am trying to achieve.

How can I make such a measure in DAX?

The data looks like this:

Thank you! 😀

[frithjof_v · Accepted solution]

This measure solved it:

Avg per city = 
CALCULATE(
    AVERAGEX(
        SUMMARIZE(
            Sales, 
                Sales[City], 
                Sales[Product], 
                "AvgAmount", AVERAGE(Sales[Amount])
        ),
        [AvgAmount]
    ), 
    ALLSELECTED(Sales[Product])
)

[HKIndia]

I think, Is this the result you want. I am getting this result without any creating measure you can see in values section in rigtht side changed to average instead of Sum. let me know if it resolved your query 

[frithjof_v]

Thank you @HKIndia,

I noticed there was an over-simplification in the sample data. I think that is why the row subtotals gave the seemingly correct answers.

However, that was not my intention. I have corrected the sample data now. 

[frithjof_v]

Here is the sample data in CSV format (semi-colon separated):

City;Product;Amount
Oslo;Apple;10
Copenhagen;Apple;20
Stockholm;Apple;30
Helsinki;Apple;40
Reykjavik;Apple;50
Oslo;Banana;50
Copenhagen;Banana;40
Stockholm;Banana;30
Helsinki;Banana;20
Reykjavik;Banana;10
Reykjavik;Grape;20
Helsinki;Grape;30
Stockholm;Grape;40
Copenhagen;Grape;50
Oslo;Grape;60
Reykjavik;Orange;5
Helsinki;Orange;10
Stockholm;Orange;15
Copenhagen;Orange;20
Oslo;Orange;25
Oslo;Carrot;5
Copenhagen;Carrot;10
Stockholm;Carrot;15
Helsinki;Carrot;20
Reykjavik;Carrot;25
Oslo;Potato;35
Copenhagen;Potato;45
Stockholm;Potato;55
Helsinki;Potato;65
Reykjavik;Potato;75
Reykjavik;Turnips;10
Helsinki;Turnips;20
Stockholm;Turnips;40
Copenhagen;Turnips;50
Oslo;Turnips;60
Reykjavik;Cauliflower;15
Helsinki;Cauliflower;25
Stockholm;Cauliflower;35
Copenhagen;Cauliflower;45
Oslo;Cauliflower;55
Oslo;Fine;35
Copenhagen;Fine;45
Stockholm;Fine;55
Helsinki;Fine;65
Reykjavik;Fine;75
Oslo;Whole grain;45
Copenhagen;Whole grain;55
Stockholm;Whole grain;65
Helsinki;Whole grain;75
Reykjavik;Whole grain;85
Reykjavik;Rhye;85
Helsinki;Rhye;75
Stockholm;Rhye;65
Copenhagen;Rhye;55
Oslo;Rhye;45
Reykjavik;Oat;75
Helsinki;Oat;65
Stockholm;Oat;55
Copenhagen;Oat;45
Oslo;Oat;55
Reykjavik;Grape;10
Helsinki;Grape;5
Stockholm;Grape;15
Copenhagen;Grape;20
Oslo;Grape;10
Reykjavik;Orange;10
Helsinki;Orange;20
Stockholm;Orange;10
Copenhagen;Orange;30
Oslo;Orange;25
Oslo;Carrot;10
Copenhagen;Carrot;25
Stockholm;Carrot;10
Helsinki;Carrot;25
Reykjavik;Carrot;15
Oslo;Potato;10
Copenhagen;Potato;50
Stockholm;Potato;30
Helsinki;Potato;20
Reykjavik;Potato;10
Reykjavik;Turnips;25