topic Re: Get Top2 values from each level in PowerBI and Dax in DAX Commands and Tips

Get Top2 values from each level in PowerBI and Dax

Anonymous — Fri, 06 Mar 2020 20:44:48 GMT

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.

Thanks,

Satya

Re: Get Top2 values from each level in PowerBI and Dax

Greg_Deckler — Fri, 06 Mar 2020 21:15:45 GMT

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

Re: Get Top2 values from each level in PowerBI and Dax

Anonymous — Fri, 06 Mar 2020 21:30:26 GMT

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

Re: Get Top2 values from each level in PowerBI and Dax

HotChilli — Fri, 06 Mar 2020 21:47:29 GMT

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

Re: Get Top2 values from each level in PowerBI and Dax

Anonymous — Sun, 08 Mar 2020 02:50:34 GMT

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