Hide value

Hello I try to walk you through:

I have a table (extract of your big one) Test_Rank

Then I do have another table Top_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.

Hi Floriankx,

sorry for late

I understand what u trying to teach me

but I told u before if data is duplicate then rankx gives same number for data.

Note:- make some value duplicates in data

Result I get :-

See Max2 is not showing because of duplicate data (max1 - 99 & Max2 - 99).

Similar case in Sub category.

Any suggestion (how I achieve my original situation)

Regards,

NICK

Hello I see,

but PowerPivot is simply amazing, I think there is an OnBoard Solution.

Check out RANKX RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

So you could add RANKX(Test_Rank;[Data];;Dense)

I think the description in the link is well done. So this should help.

Hi Floriankx,

RANKX(Test_Rank;[Data];;Dense)

Not working in my case. please try to use once in your side.

Maybe some important thing u get

Regards,

NICK

Hi all,

help to out this guyz.

Regards,

NICK

Hi Floriankx,

I was gone to trip, so could not read your msg.

Thanks for every help you did it in past few days.

Regards,

NICK