Lookup rank value?

[worthywow1988]

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!

[rohit1991]

hi @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.

[worthywow1988 · Accepted solution]

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!

[Anonymous]

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

[Anonymous]

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.

[worthywow1988]

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")

[worthywow1988]

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.

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

[worthywow1988]

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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...