Function returning the result of 3 tables relationship

[brunozanoelo]

Hey guys, I need some help developing a function that returns the following statement:

Sale 1 -> Person No. 1 -> City No. 1 -> Population of 100.000 
Sale 2 -> Person No. 2 -> City No. 2 -> Population of  40.000
Sale 3 -> Person No. 3 -> City No. 3 -> Population of  60.000
Sale 4 -> Person No. 2 -> City No. 2 -> Population of  40.000

Each sale has a corresponding Person, which has a corresponding City, which has a corresponding Population.

The DAX formula isn't working because I can't separate data values of the same Person. In case of Person 2, the correct return would be 40.000, not 80.000.

My query needs to return the average of each Sale per Population, can you guys help me? 

[v-xjiin-msft]

Hi @brunozanoelo,

With current information, it is hard for us to understand your requirement. Please elaborate your requirement by sharing us more information.

Like since there're 3 tables, what are they? What are the structures? And as you said that the DAX formula isn't working. What is this DAX formula? What's your logic to calculate the average of each Sale per Population? 

Thanks,
Xi Jin.

[brunozanoelo]

Hi @v-xjiin-msft understood.

Like since there're 4 tables, what are they?

I Have the sales table, products of the Sales, City Table and Customer Table.

The relantionship is
=> Sales
=> products of the Sales (Itens and Values)
=> Customer
=> City of the Customer

What are the structures?
The number of habitants are located in the City Table, but i can't totalize this measure using max because if the city is returned more than one time, the SUM formula will return the value of the habitants considering 2 or more times the same city.

Example:
Sale 1 - $500,00 - Customer 1 -> Population of the City 1 - 50.000
Sale 2 - $600,00 - Customer 2 -> Population of the City 1 - 50.000
Sale 3 - $500,00 - Customer 3 -> Population of the City 2 - 80.000
Sale 4 - $800,00 - Customer 1 -> Population of the City 1 - 50.000

I need this value:
$1.400 / 130.000 = $1,07 per Habitant

[Phil_Seamark]

Hi @brunozanoelo

What is the calcuation you are using?  Have you considered using a MAX of the population rather than a SUM?