Calculate revenue per capita for product in contry

[skean21]

Dear community,

I would like to calculate the revenue per capita for a product in a specified country.
I tried to put this into a dax expression, without success.

My data structure is as follows:

Table A

Country	Product	Revenue
Country A	Product A	100
Country A	Product B	200
Country B	Product A	50
Country B	Product C	100
…

Table B

Country	Population
Country A	100000
Country B	200000
Country C	50000
…

Table A and Table B are linked in both ways on the column "Country"

I have tried this DAX expression without success, for every product I get the same value.

Revenue_per_capita =

    sum('Table A'[Revenue])/

sum('Table B'[Population])

Thanks for your help.

[Anonymous · Accepted solution]

Hi @skean21  please create this measure in tableA.

Measure = 
VAR _country = MAX(TableA[Country])
VAR _population = CALCULATE(SUM(TableB[Population]),TableB[Country]=_country)
VAR _revenue = SUM(TableA[Revenue])
RETURN DIVIDE(_revenue,_population,0)

[skean21]

Spoiler

thanks a lot, that works!