Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
3 years ago

Matrix with different level

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 m...
Need Help
Bifinity_75's avatar
Bifinity_75
Solution Sage
3 years ago

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's avatar
    abukapsoun
    Post Patron
    3 years ago

    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