Forum Discussion
Lookup rank value?
Hi Rohit - many thanks for your reply - much appreciated!
In the end I got rid of the ranking tables since think was getting a bit messy. For neatness, simplicity and speed I just used min and max in the main table and then created a measure to see where the specific score sat in this range (lower, middle, top third). It's not actually ranking in the strict sense but is an ok measure in this situation since the scores are bounded and generally well distributed. Thanks again!
Hi Jian - thanks for your reply - much appreciated.
I've been trying different approaches and combinations all day but not getting closer 😓
I've created a new example and put it on OneDrive - hopefully you can download the pbix and source xls Example 2
What I'm trying to achieve is to get a lower third percentile and upper third percentile for the measure "Positive %" grouped by level and question. I then want to use this to check where the score in the little table on the left above sits ("Low" if below the lower percentile, "High" if above the upper percentile and "Middle" if in between. In excel I have checked/calculated the values I want (in green) but always seem to get the overall values in pink
I have tried filtering, ALLEXCEPT etc. but nothing seems to work!
Any help/advice welcome!
Super UserYou still use measures to formulate your RANKING_TABLE. What's the intent? Are these values impacted by user interaction ?
Here's an alternative approach
RANKING_TABLE = ADDCOLUMNS(filter(TABLE_DATA,[Sales]>0),"Sales Adjusted",[Sales]*1.2)
Quartile Label =
var p = [Product]
var LT= PERCENTILEX.INC(FILTER(RANKING_TABLE,[Product]=p),[Sales Adjusted], 0.33)
var MT= PERCENTILEX.INC(FILTER(RANKING_TABLE,[Product]=p),[Sales Adjusted], 0.66)
RETURN SWITCH(TRUE(),
[Sales Adjusted]<LT,"LOW",
[Sales Adjusted]<MT,"MED","HIGH")
Thanks - I'll try the alternative approach tomorrow!
As a bit more background what I've actually got is survey information consisting of summary scores at different regional levels so something like this
Region Question Response Number of Responses Continent A Q1 Yes 30 Continent A Q1 No 10 Continent A Q2 Yes 24 Continent A Q2 No 16 Country X Q1 Yes 10 Country X Q1 No 5 Country X Q2 Yes 8 Country X Q2 No 7 City Q Q1 Yes 2 City Q Q1 No 2 City Q Q2 Yes 3 City Q Q2 No 1 A continent has several countries and each country has several cities. I only have summary level information and there is an algorithm used by our provider which means I can't add up responses from lower levels i.e. the continent score is not exactly the sum of the country scores.
I've created a dashboard where the user can select a continent, country or city to view the detail. So for example the manager in charge of City Q selects this and all the results are shown. In this simplified example it's the Yes % (i.e. a measure). I also display the hierarchy so they can switch to their country or continent to see those results.
As part of the info I want to show where they sit relative to the other areas at the same regional level e.g. Out of the 30 cities, City Q is 15th so is in the "middle". So I'm trying to work out the percentiles for each question at the selected regional level and then compare to the selected city.
I've tried a few things but nothing's quite working - one issue I have is the question and regional areas are linked tables to the results table and don't seem to be able to use them in filters/ALLEXCEPTs etc.
Thanks again!
- lbendlin
Thank you for the extra context, that helps.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.