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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.