cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
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
Census Data Age Group1.PNG
 
Thanks in advance
 
1 ACCEPTED SOLUTION
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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 week 27.PNG trying to figure out where the calculations are deviating.

Thanks for the help so far @mahoneypat 

@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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.