Get Top2 values from each level in PowerBI and Dax

[Anonymous]

Hi, 

I am trying to achive o/p like below with given dataset in PowerBi and Dax. 

Can you please help.

Req : I need to get top 2 values dynamically in each level of hierarchy. 

Input data and Required output

Thanks,

Satya

[Greg_Deckler]

Could you post that source data as text? Looks like you want a dynamic RANKX measure and those are always a treat so need sample data to work with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thank you for helping @Greg_Deckler . Below is sample data. Please let me know if need more details.

Area	Segment	Product	Value
A1	S1	P1	200
A1	S1	P2	100
A1	S1	P3	50
A1	S2	P2	400
A1	S2	P4	240
A1	S2	P5	310
A1	S2	P6	160
A1	S3	P3	300
A1	S3	P4	250
A2	S1	P8	150
A2	S1	P1	250
A2	S1	P6	600
A2	S2	P1	700
A2	S2	P2	100
A2	S2	P3	400
A2	S3	P4	350
A3	S1	P1	100

[HotChilli]

A measure

MeRank = RANKX(
            FILTER(ALL( TableY),  TableY[Segment] = MAX(TableY[Segment]) && TableY[Area] = MAX(TableY[Area])), 
                CALCULATE(SUM(TableY[Value])))

If you put Area, Segment, Product as Rows.  Value in Values.

Add the MeRank to the Visual Filter and make it 'less than' 3

[Anonymous]

@HotChillithanks for the reply. tried as you suggested but i am getting the same value (as 1) for all levels.

Tried with below measure but i am getting top 2 values for entire segment level. But i need top2 values for each area as highlighted below. please help

TopN =

VAR ranks =

RANKX(CROSSJOIN(ALLSELECTED(Data[Area]),ALLSELECTED(Data[Segment])),CALCULATE(SUM(Data[Value])),,DESC,Dense)

RETURN IF(ranks <= 2,SUM(Data[Value]),BLANK () )

Also tried with below measure, but is it working only for specific level(here segment as it fixed),when i drill up/down it is not working.

Top2_Segments = VAR  R = VALUES((Data[Segment])) Return CALCULATE([Value_M],TOPN(2,ALL(Data[Segment]),[Value_M]),R)

Thanks,

Satya