Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
skean21
Regular Visitor

Calculate revenue per capita for product in contry

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

CountryProduct Revenue
Country AProduct A100
Country AProduct B200
Country BProduct A50
Country BProduct C100
  

 

Table B

CountryPopulation
Country A100000
Country B200000
Country C50000
 

 

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.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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)

 

 

Spoiler
thanks a lot, that works!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors