cancel
Showing results for
Did you mean:
Helper III

Dividing two values in different tables

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

1 ACCEPTED SOLUTION
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

3 REPLIES 3
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper III

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

Super User IV

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

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!