## Filter a table of calculated measures [Solved]

Anything related to PowerPivot and DAX Formuale
Steve_D
Posts: 52
Joined: Sun Jul 31, 2016 2:29 pm

### Filter a table of calculated measures

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/cumulative-running-total-based-on-highest-value/

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.

MattAllington
Posts: 922
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)?
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/what-is-power-pivot/
http://xbi.com.au/learndax

Steve_D
Posts: 52
Joined: Sun Jul 31, 2016 2:29 pm

### 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.

MattAllington
Posts: 922
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Filter a table of calculated measures  [Solved]

filtered running total.xlsx

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/what-is-power-pivot/
http://xbi.com.au/learndax

MattAllington
Posts: 922
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

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/what-is-power-pivot/
http://xbi.com.au/learndax

Steve_D
Posts: 52
Joined: Sun Jul 31, 2016 2:29 pm

### 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.
Attachments
National Population by Pcode.xlsx

Steve_D
Posts: 52
Joined: Sun Jul 31, 2016 2:29 pm

### 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.

MattAllington
Posts: 922
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/what-is-power-pivot/
http://xbi.com.au/learndax

Steve_D
Posts: 52
Joined: Sun Jul 31, 2016 2:29 pm