cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Measure to divide certain rows in a column by certain rows in another column from a different table

Hi there!

I am analysing child fostering data, and want to work out the number of fostered children per 1,000 people aged below 15 in different areas

I have two data tables, 1 and 2.

1 has columns: area name; number of children in foster care

2 has columns: area name; total population aged under 15

I have managed to divide the SUM of children in foster care by the SUM of population aged under 15, but I need to do this for each area (row), as opposed to the whole column. I hope that makes sense. Here is my current measure, which doesn't take account of area:

per 1000 under 15 =
VAR num = SUM('3 - fostering_data 2018-19'[Value])
VAR tot = CALCULATE(SUM('Population Estimates (0-15)'[OBS_VALUE]),ALL('Population Estimates (0-15)'[C_AGE_NAME]))
RETURN DIVIDE(num*1000,tot)

Many thanks
5 REPLIES 5
Super User IV

## Re: Measure to divide certain rows in a column by certain rows in another column from a different ta

In such cases , I prefer to create a column and bring the value in table .

Example

New column in Table 1 = maxx(filter(table2,table1[customer] = table2[customer] && table2[option]="construction",table2[value])

New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Microsoft

## Re: Measure to divide certain rows in a column by certain rows in another column from a different ta

Hi @jlatimer ,

What is the relationship between the two tables?
In my test, the two tables have “1-1” relationship.
You can try DAX like this:
per 1000 under 15 =
var per_area = DIVIDE(
SUM('Sheet3'[number of children in foster care]),
SUM('Sheet4'[total population aged under 15])
)
return per_area*1000

If the problem persists,could you please share sample data or sample pbix?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Measure to divide certain rows in a column by certain rows in another column from a different ta

Hi @V-lianl-msft , thanks for this!

The DAX you suggested has 90% solved the problem.

The issue is that a single-single relation is not possible, because my fostering data table contains not just counts of children in care, but also other measures, meaning for each area there are multiple rows. Similarly, my population data has multiple years.

In the query editor, I filtered out the other years from my population data, and the other measures from the foster care data. This allowed the DAX you suggested to return the correct value - however, it means i can't use any of my other data.

Do you know a way I can apply these filters using the measure (DAX) rather than the query editor, so that i can use all of my data? I hope that makes sense!

Thank you,

Jonny

Microsoft

## Re: Measure to divide certain rows in a column by certain rows in another column from a different ta

It is very difficult to analyze without looking at the data and just by imagining. See if you can share sample data or sample pbix.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Measure to divide certain rows in a column by certain rows in another column from a different ta

Thanks Liang

I'm not quite sure how to share the .pbix here, and the data is on my company's sharepoint so i'm not sure if it would work anyway - i'll summarise the data here (the tables actually contain 1,000s of rows but are of the same format). Table 1 areas are English local areas + England itself, table 2 is just English local areas.

table 1:

 Indicator Area Value Number of children in care Tameside 53 Number of children in care England 103,402 Number of places available Tameside 55 Number of places available England 106,321

table 2:

 Date Area Population under 15 2019 Tameside 42,123 2018 Tameside 43,232 2019 Manchester 101,122 2018 Manchester 104,001 201X Y English local area N

geography lookup table:

 Sub-area Sub-area type Area Area type Tameside English local area England Country Manchester English local area England Country Y English local area England Country

So, my measure needs to sum just one type of indicator, per each area, from table 1 - and dividie this by the sum of population under 15 per area from table 2. Does that make sense? One final issue is that table 2 does not have a row for England - the value for England is the sum of all the local areas.

Many thanks,

Jonny

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!