Getting values from average values based on criteria

[newbiepowerbi]

Hello, i have 2 tables loaded in the data model. The first table shows the historical values based on certain cities or location. 

City	Country	Continent	Value
New York	US	North America	240
Toronto	Canada	North America	600
Detroit	US	North America	400
Barcelona	Spain	Europe	350
London	UK	Europe	280
Beijing	China	Asia	180
Shanghai	China	Asia	380
Los Angeles	US	North America	280
London	UK	Europe	200

my goal is to come up with an average value based on specified city, country and continent. Such average values will be used to come up with the value for the second table 

City	Country	Continent
Boston	US	North America
Madrid	Spain	Europe
Detroit	US	North America
Berlin	Germany	Europe

By using a logicial function wherein I will use the average value of said city if the city exist in the first table else use average value of said country if such country exist or else use the average value continent instead. 

Appreciate your help on how i can do this in Dax. Thanks

[kentyler]

i have rethought this

i think you need to convert the top table into three tables

one for city

one for country

one for continent

each should have 2 columns, names in one column, values in the other

and each should only have unique names, cities should only appear once in the city table

countries should only appear once in the country table

continents should only appear once in the continent table

when you reduce the tables to distinct names, you average the values

so in the country table, for example, each distinct country has the average values for the different times it was entered.

You can create these three tables in your data source, or in power query when you load the data.

then you load all three tables along with the second table

you create a one to many relationship between city in the second table and city in the city table

and between country in the second table and country in the country table

and between continent in the continent table and continent in the continent table

since the 3 tables you created will be on the one side of one to many relationships with the second table, you will be able to create measure for the second table to look up the already computed average values in the three tables you created using the RELATED() function. these three measure will give you an average (if it exists) for city, country and continent on each line of your second table. a 4th measure can then pick which value to use, depending on which of the measures return values.

If this is to abstract I will try to create some tables in excel and make a sample data model in power bi for you.

[newbiepowerbi]

Hello,

Kindly confirm if i'm following it correctly,  convert the top table into three tables (should i do it using PQ create 3 queries using grouped by function? or there is an easy to do it through dax like calculate table?)

create 3 relationship from the second table to the 3 tables. Using each relationship, create a measure to get the value from each of the created 3 tables. Hence, the measure will either get the value from the 3 tables or blank (if it not exist). Then create a 4th measure to choose which among the 3 measure should you get the value. Will this create additional 4 columns for the second table or can it be done internally hence will only create 1 column for the 4th measure?

Appreciate if you can illustrate. thanks very much

[newbiepowerbi]

Hello hope you are still here or anyone can join to share their insights. Anyway, it turns out, both tables all came from a single fact table that were differentiated by the status

Status	City	Country	Continent	Amount
Close	New York	US	North America	240
Close	Toronto	Canada	North America	600
Close	Detroit	US	North America	400
Close	Barcelona	Spain	Europe	350
Close	London	UK	Europe	280
Close	Beijing	China	Asia	180
Open	Boston	US	North America
Open	Madrid	Spain	Europe
Open	Detroit	US	North America
Open	Berlin	Germany	Europe

Given the above table, my goal is to provide a value for the open status based on the average values provided by the close status. Hence for open status Detroit, to get average value for Detroit, then for open status Boston, since close status does not have value for Boston, it will get value from the average value of US. 

I prefer if i can do this via a measure instead of a calculated column. Appreciate if anyone can help me on this. thanks

[kentyler]

Write 3 helper measures:

Average for City

Average for Country

Average for Continent

Since measures can refer to other measures then you can write a measure that does your if logic to see which average is present for a given combination

[kentyler]

if ISBLANK([average for city]),
      if(ISBLANK([average for country]),
              [average for continent]),
      [average for country]),
[average for city])

[newbiepowerbi]

Thanks for the feedback, the measure below will be placed in the second table? should i establish a relationship between two table?