Forum Discussion
Hide value
Hello,
I couldn't test 'Dense' last week so I did it today.
My Measure is TopN:=CALCULATE(MAX(Test_Rank[Data]);FILTER(Test_Rank;RANKX(Test_Rank;[Data];;;Dense)=Top_3[MaxN]))
This is my over all:
For me it looks like it is supposed.
Hello I try to walk you through:
I have a table (extract of your big one) Test_Rank
| Sub | Sub Category | Data |
| A | GH | 99 |
| B | IJ | 96 |
| B | IJ | 95 |
| A | CD | 94 |
| A | EF | 90 |
| B | KL | 89 |
| A | EF | 87 |
| A | GH | 95 |
Then I do have another table Top_3
| Top 3 |
| 1 |
| 2 |
| 3 |
Tables are not related.
In Top_3 i have the measure MaxN:=MAX(Top_3[Top 3])
In Test_Rank I have TopN:=CALCULATE(MAX(Test_Rank[Data]);FILTER(Test_Rank;RANKX(Test_Rank;[Data])=Top_3[MaxN]))
MAX is just for aggregation, Filter should return only one value but it has to be wrapped by aggregation funtion (Min, Max, Sum, etc. I don't recommend Sum in case Filter returns more values, duplicats e.g.)
Filter is for Filtering. RANKX ranks your Data. In combination only the value is filtered of TopN value is selected depending on MaxN.
If I create PivotTable with this data it returns the result as shown before. I hope things are clearer now.
Best regards.