Forum Discussion
Lookup rank value?
Hi!
I have data where for a selected area I want to show the relative position compared to all the other areas. I've created a simple example:
| Area | Product | Group | Sales |
| North1 | ABC | A | 100 |
| North2 | ABC | A | 0 |
| North3 | ABC | A | 203 |
| North4 | ABC | A | 13 |
| North5 | ABC | A | 403 |
| North6 | ABC | A | 76 |
| North7 | ABC | A | 50 |
| North8 | ABC | A | 2 |
| North1 | DEF | A | 38 |
| North2 | DEF | A | 0 |
| North3 | DEF | A | 130 |
| North4 | DEF | A | 32 |
| North5 | DEF | A | 0 |
| North6 | DEF | A | 20 |
| North7 | DEF | A | 10 |
| North8 | DEF | A | 40 |
| North1 | GHI | B | 33 |
| North2 | GHI | B | 2 |
| North3 | GHI | B | 44 |
| North4 | GHI | B | 2 |
| North5 | GHI | B | 403 |
| North6 | GHI | B | 76 |
| North7 | GHI | B | 5 |
| North8 | GHI | B | 2 |
| North1 | JKL | B | 38 |
| North2 | JKL | B | 453 |
| North3 | JKL | B | 6 |
| North4 | JKL | B | 32 |
| North5 | JKL | B | 55 |
| North6 | JKL | B | 20 |
| North7 | JKL | B | 34 |
| North8 | JKL | B | 40 |
I've created a rank table
RANKING_TABLE = filter(SUMMARIZE (TABLE_DATA,[Area],[Product],"Sales Adjusted",[Sales Adjusted] ),TABLE_DATA[Sales Adjusted]>0)
In the TABLE_DATA_SUMMARY in this example I would like to show the quartile labels North1 for ABC and DEF as calculated in the RANKING_TABLE
I've just pulled "Upper Quartile" into the summary to show what I'm after - appreciate this wouldn't/doesn't work!
I've tried LOOKUPVALUE but didn't seem to work - should I be using this or another approach?
The pbix file is here Ranking Example.pbix
Any advice really appreciated!
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!
11 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
rohit1991hi worthywow1988 ,
Your goal is to display the Upper Quartile Label from the RANKING_TABLE in the TABLE_DATA_SUMMARY. Since LOOKUPVALUE is not working as expected, try using CALCULATE with FILTER instead.
1. Create a Measure for Lookup: Use CALCULATE to find the quartile label based on Area and Product:
Upper_Quartile_Label_Summary = VAR SelectedArea = SELECTEDVALUE(TABLE_DATA[Area]) VAR SelectedProduct = SELECTEDVALUE(TABLE_DATA[Product]) RETURN CALCULATE( MAX(RANKING_TABLE[Upper Quartile Label]), RANKING_TABLE[Area] = SelectedArea, RANKING_TABLE[Product] = SelectedProduct )2. Add the Measure to TABLE_DATA_SUMMARY
- Place Upper_Quartile_Label_Summary in your TABLE_DATA_SUMMARY table.
- This will fetch the corresponding quartile label for the selected Area and Product.
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,worthywow1988 . It's great to see that you solved your problem and that you shared the method to the forum,
you can mark your option as a solution which will help other users in the forum.I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian
- lbendlin
You are creating a calculated table based on a measure. While technically possible, it does not make sense as there is no filter context. Please explain the 1.2 factor.
Hi! This was just a quick simple example so will be slightly out of context. The 1.2 factor was just a dummy factor since wanted to show the summary ranking table contains measures not just values.
To recap what I want to do is for a selected Area (e.g. North 1) and a selected Group (e.g. A) show where North 1 sits relative to the other areas. My actual dashboard is actually using third "quartiles" (tritiles?!). I've updated the example here Example v2 . So in the TABLE_DATA_SUMMARY I want the quartile to show as "HIGH" for ABC and "MID" for DEF from the ranking table
Hi,lbendlin ,thanks for your concern about this issue.
Your answer is excellent!
I tried to open your pbix but it failed due to environmental reasons, would you be able to share your pbix test file again by way of github/OneDirve (please don't encrypt it)
It looks like you tried to place the already created MEASURES into a calculation table that you created yourself and tried to filter them through the slicer.
As lbendlin mentioned, your requirement is feasible from an implementation point of view.
You could try using addcoulmns .ADDCOLUMNS function (DAX) - DAX | Microsoft Learn
It is important to note:
Measure requires a specific calculation context in DAX (Data Analysis Expressions) to be calculated correctly. If the necessary context is missing from the calculation table, the measure may not return the expected result.
I recommend that you place the creation of the measure in the calculation table when you create it (as a variable spliced into this calculation table)but it is important to note that the calculation table is static, while the individual measure is dynamic (the calculation table is not affected by the slicer, while the individual measure is affected by the slicer)
URL:Table Filter does not work based on slicer selecti... - Microsoft Fabric Community
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.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!
- lbendlin
You 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")
