Dividing two values in different tables

[Anonymous]

I currently have 2 tables: Sheet 1 and Census data.

I have built the census data to show:

Census Data Age/Gender = 
DATATABLE(
 "Age Group Name",STRING,
 "Male",INTEGER,
 "Female",INTEGER,
 "Total",INTEGER,
 {
 {"0-4 yrs",169731,161784,331515},
 {"5-14 yrs",344848,330189,675037},
 {"15-24 yrs",292492,283960,576452},
 {"25-34 yrs",318401,341009,659410},
 {"35-44 yrs",368527,378354,746881},
 {"45-54 yrs",311036,315009,626045},
 {"55-64 yrs",252556,256402,508958},
 {"65-74 yrs",184462,189046,373508},
 {"75-84 yrs",89313,107191,196504},
 {"85+ yrs",23062,44493,67555}
 }
)

What I'm trying to do is take the count per age group in Sheet1 and multiply it by the Total in the census table

e.g. first value of 0-4 yrs  is 5 therefore (5/331515)*100000 = 1.5

Thanks in advance

https://www.dropbox.com/s/c3hfci8jabydxv1/Pareto.pbix?dl=0

[mahoneypat · Accepted solution]

You need to use the Age Group column from your Census table in the matrix instead, and then you can use this measure to get your result.

NewMeasure =
DIVIDE (
    COUNT ( Sheet1[Event ID] ),
    AVERAGE ( 'Census Data Age/Gender'[Total] )
) * 100000

Regards,

Pat

[Anonymous]

Followed what you said there and my numbers seem a little off. E.g. week 27  0 -4yrs has 6 counts so should be divided by 331515 *100000 = 1.8

where i get  trying to figure out where the calculations are deviating.

Thanks for the help so far @mahoneypat 

[mahoneypat]

@Anonymous  In my file, I do have 1.8 for 27 weeks 0-4 yrs.  Do you have other filters on the visual?

Regards,

Pat