Matrix with different level

[abukapsoun]

Hi, 

Need your kind support with the following:

I have the following dataset,

https://drive.google.com/file/d/1jI-B_Hmk-pLHH-UpLfk6ZC7r1IcAir-g/view?usp=share_link

And I want to have a matrix view, However not able to reach the end result 

The total per country highlighed below should be the sum of city1 + city2 and not the average. 

City1 value = average of all values in continent1 + average of all values in continent2 during day1 (values at 12:00 and values at 01:00)

City2 value = average of all values in continent1 + average of all values in continent2 during day1 (values at 12:00 and values at 01:00)

Value of country should be city1 + city2 during day. 

[Bifinity_75]

Hi @abukapsoun , try this new measure:

Value2 = 
var _table=SUMMARIZE(Sheet1,
Sheet1[City],
"_avg", AVERAGEX(Sheet1, Sheet1[Value])
)
RETURN

var _table2=SUMMARIZE(Sheet1,
Sheet1[Date & Time.1],
"_sum", AVERAGEX(Sheet1, Sheet1[Value])
)
RETURN
var sum_row=SUMX(_table2,[_sum])

var sum_avg=SUMX(_table,[_avg])
RETURN

if(HASONEVALUE(Sheet1[Date & Time.1]),
if(ISINSCOPE(Sheet1[City]),AVERAGE(Sheet1[Value]),
sum_avg
), sum_row)

I hope works for you. Best regards!

[abukapsoun]

Sorry @Bifinity_75 but it didn't work. The value displayed is still the average. Even the subtotal is now the average of all values. So, I am a step backward:( 
Values of country should be average in continent1 + average in continent2 

[abukapsoun]

@Bifinity_75 the value of city1 displayed should be (47.92 + 19.03) and value of city2 should be (83.58+16.63)

value of country should be city1+city2 

[Bifinity_75]

Hi @abukapsoun , try this measure:

Value2 = 
var _table=SUMMARIZE(Sheet1,
Sheet1[City],
"_avg", AVERAGEX(Sheet1, Sheet1[Value])
)
RETURN
var sum_avg=SUMX(_table,[_avg])
RETURN

if(ISINSCOPE(Sheet1[City]),AVERAGE(Sheet1[Value]),
sum_avg
)

Best regards

[abukapsoun]

hi @Bifinity_75 

Thank you very much! I am almost there. I can now see the total summing the values. But still one thing to go, I appreciate if you can help further with it. I have noticed, it seems the result per city is averaging all the values and not taking into consideration the following:

Final result = Average values in continent1 + Average values in continent2

Thank you