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

Postby Steve_D » Tue Apr 11, 2017 4:14 pm

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: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Filter a table of calculated measures

Postby MattAllington » Fri Apr 14, 2017 7:52 am

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

Postby Steve_D » Fri Apr 14, 2017 11:58 am

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: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Filter a table of calculated measures  [Solved]

Postby MattAllington » Fri Apr 14, 2017 1:39 pm

How about this?

filtered running total.xlsx
(166.97 KiB) Downloaded 29 times


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.

Image
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: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Filter a table of calculated measures

Postby MattAllington » Fri Apr 14, 2017 1:43 pm

The ranked postcode formula is wrong. Use this instead



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/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

Postby Steve_D » Fri Apr 14, 2017 2:05 pm

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
(116.98 KiB) Downloaded 21 times

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

Re: Filter a table of calculated measures

Postby Steve_D » Fri Apr 14, 2017 3:19 pm

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: 898
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Filter a table of calculated measures

Postby MattAllington » Fri Apr 14, 2017 3:35 pm

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

Re: Filter a table of calculated measures

Postby Steve_D » Fri Apr 14, 2017 4:29 pm



Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest