Forum Discussion
Getting values from average values based on criteria
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