Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,
 
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.
Anonymous
Not applicable

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

 

Hi @Anonymous ,
 
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.
Anonymous
Not applicable

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

amitchandak
Super User
Super User

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])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.