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