bottom 5 dax not working

[ROG]

Hello Community,

The dax below supposed to give me the bottom 5 carriers + the remaining (Others). Bottom is this case means negative No of Port-Outs, so I was able to get the Top5, and added the 'DESC' funcion to give the negative values. But the result is showing only the 'Others' and I'm missing the Bottom 5 Carriers. Can you help, please? How to have a dax that can show me the bottom 5 + the remaing?

TopN No Port-Outs =

VAR TopN_Table =

TOPN(

    5,

    ALLSELECTED(CarrierList),

    [No Port-Outs],DESC

)

VAR TopN_KeepFilters_NoPortOuts =

CALCULATE(

    [No Port-Outs],

    KEEPFILTERS(TopN_Table)

)

VAR Total_NoPortOuts =

CALCULATE(

    [No Port-Outs],

    ALLSELECTED(CarrierList)

)

VAR TopN_NoPortOuts =

CALCULATE(

    [No Port-Outs]

    ,TopN_Table

)

VAR Others_NetPorts = Total_NoPortOuts - TopN_NoPortOuts

VAR Others_isSelected = SELECTEDVALUE(CarrierList[carrier]) = "Others"

RETURN

IF (ISINSCOPE(CarrierList[carrier]), IF(Others_isSelected, Others_NetPorts, TopN_KeepFilters_NoPortOuts), Total_NoPortOuts)

Expected result

Real - wrong result

 

Thank you!

[Anonymous · Accepted solution]

Hi @ROG ,

Based on the information you have provided, you can follow these steps:

1.Add new column

ranking =

RANKX ( ALL ( 'Table' ), 'Table'[No Port-Outs],, DESC, DENSE )

2.Add new column

new carrier =

IF ( 'Table'[ranking] <= 5, 'Table'[carrier], "Others" )

3.Put in Matrix

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[ROG]

That works! Many thanks @Anonymous