Dynamic total with sum and average

[Anonymous]

Dear All,

i am using a Matric in Power BI desktop and i have data like below

I know how to dynamically manipulate the grand total as average.

but here i need total column of Uk with sum and other rows with average.

could you please help me out with logic?

refer to this link for more.

https://docs.google.com/spreadsheets/d/1ZJz1XlfpGcnBzaiC070lAfJpjy2_1VhRcj541EcHNSo/edit?usp=sharing

@amitchandak @Pragati11 

[Jihwan_Kim]

UK sum and others avg : =
IF (
HASONEVALUE ( Countries[Country] ),
SWITCH (
SELECTEDVALUE ( Countries[Country] ),
"UK", SUM ( Data[Value] ),
AVERAGE ( Data[Value] )
)
)

Link to the sample pbix file 

[Anonymous]

@Jihwan_Kim  i mean what if i have states, cities and county level drill down fields in the matrix? the same measure will work for the low granularity level values?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Can you share your sample pbix file? I think it depends on how your model looks like, and also it depends on how the countries - states - cities are related.

[Anonymous]

@Jihwan_Kim  thnaks for valuable time in looking into this issue.

i dint understand your question.

country will be linked to state and state will be linked to city.

when we expand the country in matrix, there will be states appear in that case our DAX measure we wrote for country how it will be impact the state level data in the same matrix?

[Anonymous]

will this work if i add more sub categories in the matrix?