Hide value

[Naveennegi119]

Hi All,

It really very complicated to me to resovle.

when I select sub then it show fine top 3 value (situation 1)

 

But when i select both(sub and sub category), it show the value sub and sub category. see below:-

 

Concatenate = CONCATENATE(Test[Sub],Test[Sub Category])

data for use(it showing error massage cannot exceed 20,000 charactars) that's why image attached

NICK
Regards, 

[Floriankx]

Hello why don't you rank all your data with a measure and then let the slicer do the rest?

TopN:=CALCULATE(MAX(Test_Rank[Data]);FILTER(Test_Rank;RANKX(Test_Rank;[Data])=[MaxN]))

MaxN:=Max(Top_3[ID] which is a simple table containing heading and the values from 1 to 3.

[Naveennegi119]

Hi @Floriankx

thanks for sharing idea,

but rankx give same number for duplicate data

Regards,

NICK

[Floriankx]

Hello can you make a screenshot?

[Naveennegi119]

Hi @Floriankx,

your formula gives me max value of A & B,

not give 2nd max and 3rd max.

let me clear once again what i need

Condition - 1

if I select sub slicer then I get top 3 value of data. I'm using index method for that and i get top 3 value of sub, So first situation is done

But

Condition - 2

when I select Both(Sub and Sub category) then I get top 3 value of data using the same method of index, that's fine too (as you see my previous attach images)

Now problem is when i go with condition - 2  condition - 1 also appearing

that what i what to hide

Using condition-1 condition-2 hide

Using condition-2 conditon-1 hide

Hope u understand what i'm saying

Regards,

NICK

[Floriankx]

Hello,

Your MaxN is wrong. I ignored your calculated column and tried to build an independent measure only needing the columns category, sub category and Data.

MaxN should relate to an independent table, which helps you to create Max1, Max2, Max3.

If you want to display Situation 1 only if now Sub Category is selected, maybe this helps:

Situation 1: IF(NOT(HASONEVALUE([Sub Category])),[TopN],BLANK())

Situation 2: IF((HASONEVALUE([Sub Category])),[TopN],BLANK())

Maybe this helps.

[Naveennegi119]

Hi @Floriankx,

I'm confuse with MaxN

How I get Max1, Max2 & Max3

can u please sent a screenshot

Regards,

NICK

[Floriankx]

Hello, I do have them as column in my pivot table.

A is sliced.

[Naveennegi119]

Hi @Floriankx,

you giving me solution

I want to know how i get solution step by step

Regards,

NICK

[Floriankx]

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.

[Naveennegi119]

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

Sub	Sub Category	Data
A	CD	94
A	CD	94
A	CD	69
A	CD	55
A	CD	53
A	EF	90
A	EF	87
A	EF	87
A	EF	71
A	EF	53
A	GH	99
A	GH	99
A	GH	78
A	GH	75
A	GH	62
B	IJ	96
B	IJ	95
B	IJ	77
B	IJ	69
B	IJ	61
B	KL	89
B	KL	87
B	KL	64
B	KL	63
B	KL	60
B	MN	81
B	MN	80
B	MN	77
B	MN	61
B	MN	56

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

[Floriankx]

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.

[Naveennegi119]

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

[Floriankx · Accepted solution]

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.

[Naveennegi119]

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

[Naveennegi119]

Hi all,

help to out this guyz.

Regards,

NICK