cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jlatimer Frequent Visitor
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)
 
Can anyone advise?
 
Many thanks
5 REPLIES 5
Super User IV
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])




Did I answer your question? Mark my post as a solution!
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 V-lianl-msft
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.
per_1000_under15.PNG
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.
jlatimer Frequent Visitor
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 V-lianl-msft
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.
jlatimer Frequent Visitor
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:

IndicatorAreaValue
Number of children in careTameside53
Number of children in careEngland103,402
Number of places availableTameside55
Number of places availableEngland106,321

 

table 2:

DateAreaPopulation under 15

2019

Tameside42,123
2018Tameside43,232
2019Manchester101,122
2018Manchester104,001
201XY English local area N

 

geography lookup table:

 

Sub-areaSub-area typeAreaArea type
TamesideEnglish local areaEnglandCountry
ManchesterEnglish local areaEnglandCountry
YEnglish local areaEnglandCountry

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors