Forum Discussion
Getting values from average values based on criteria
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
6 Replies
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
if ISBLANK([average for city]),
if(ISBLANK([average for country]),
[average for continent]),
[average for country]),
[average for city])
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.
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
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
