Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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
 
Thanks in advance
 
COUNT
crossjoin
Data Source
divide
Table
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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
     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        mahoneypat
        Microsoft Employee
        5 years ago

        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