I have a table which shows Post code and population. I've created calculated measures which rank the Population in descending order, A cumulative total of population based upon Rank, and calculated the percentage the cumulative total is of the Total Population. For an idea of what the DAX equations look like refer to Matt's blog http://exceleratorbi.com.au/cumulativerunningtotalbasedonhighestvalue/
However, I want to take this a step further and now want to filter the output via a harvester measure so only the top 80% are displayed (remember the % of total is a calculation, so using a calculation to filter a calculation doesn't work). Sounds easy doesn't it. I thought so to but, I've spent days using combinations of calculates, filters, summarize, and calculatetables to no avail. Any advice would be appreciated.
Filter a table of calculated measures [Solved]

 Posts: 881
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Filter a table of calculated measures
Hi Steve
By top 80%, do you mean keep all the postcodes where the cumulative % is less than or equal to 80?
Also, where did you source this data (out of interest)?
By top 80%, do you mean keep all the postcodes where the cumulative % is less than or equal to 80?
Also, where did you source this data (out of interest)?
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
Re: Filter a table of calculated measures
Yes, BTW, I got around this by exporting the postcodes and populations to excel and hard coded the cumulative percentage in. However, I could do that because there are only 2,467 post codes. .
The population data by post code can be downloaded from the ABS web site. I've only extracted to Post Code level, I understand there are lower levels than post code (SC2 I think they call it) that's available with a premium subscription.
The population data by post code can be downloaded from the ABS web site. I've only extracted to Post Code level, I understand there are lower levels than post code (SC2 I think they call it) that's available with a premium subscription.

 Posts: 881
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Filter a table of calculated measures [Solved]
How about this?
All I did was break the problem into pieces and solved one formula at a time (with a hard coded value for Percentile until it was all working). I wrote the formulas from left to right, one at a time, getting it to work before moving on.
All I did was break the problem into pieces and solved one formula at a time (with a hard coded value for Percentile until it was all working). I wrote the formulas from left to right, one at a time, getting it to work before moving on.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax

 Posts: 881
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Filter a table of calculated measures
The ranked postcode formula is wrong. Use this instead
Also, can you please post a link for the PostCode download
Also, can you please post a link for the PostCode download
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
Re: Filter a table of calculated measures
I don't know how you do it... I hadn't even considered that approach.
Here's the Post codes and populations. This is the old (2011) census data, the 2016 data will be out later this year.
Here's the Post codes and populations. This is the old (2011) census data, the 2016 data will be out later this year.
 Attachments

 National Population by Pcode.xlsx
 (116.98 KiB) Downloaded 14 times
Re: Filter a table of calculated measures
There are a number of Pcodes that have equal populations and when using the Rankx function you may need to use the "Skip" or "Dense" switch. I used "Skip", it seems to return a more accurate running total. However, you will have Pcodes that have identical percentiles.

 Posts: 881
 Joined: Sun May 04, 2014 4:01 pm
 Location: Sydney, Australia
Re: Filter a table of calculated measures
Thanks for the data, but can you please share the original link? I struggle to find what I need on the abs site
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
http://exceleratorbi.com.au/whatispowerpivot/
http://xbi.com.au/learndax
Re: Filter a table of calculated measures
Download the file you want from here,
http://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/3235.02015?OpenDocument
http://www.abs.gov.au/AUSSTATS/abs@.nsf/DetailsPage/3235.02015?OpenDocument
Who is online
Users browsing this forum: No registered users and 1 guest