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 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. 

 

Need Help

5 Replies

  • 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

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

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

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

        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