Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
snandy2011
Helper IV
Helper IV

Problem with rankx function for bottom

Hi,

I just want to calculate bottom customer but having little problem with my DAX.

 

The scenario is that, I have a slicer with 3 values (Profit,Profit Margin,Sales).and on my another slicer where i have created bottom 5,bottom 10 etc. when i select Sales and bottom 5 it's only shows 4 value.but, if i choose profit or profit margin and bottom 5 or 10, strangely it is showing correct value.

 

I don't know how it is possible?

 

Here is my list of  formula,

 

Rank from bottom = RANKX(ALL('Top Analysis Unpivot'[Value]),'Top and Bottom Analysis Measures'[Dynamic],,ASC,Dense)

 

Rank Selection for Bottom = IF(HASONEVALUE('Ranking For Bottom'[Rank] ),VALUES('Ranking For Bottom'[Rank Number]),MAXX(VALUES('Top Analysis Unpivot'[Value]),[Rank from bottom]))      [ This formula selects the rank number from my rank slicer)

 

Bottom  value = IF([Rank from bottom] <=[Rank Selection for Bottom],[Dynamic])    [ This formula shows the ultimate value]

 

please Have a look on the screenshots

 

Screenshot of Sales.pngBut when i choose profit from the slicer it works fineBut when i choose profit from the slicer it works fine

 

Can you identify what is going on? why it is showing only 4 values instead of 5?

 

Any suggesation is really appreciable.

 

Thanks,

snandy

 

 

 

5 REPLIES 5
MFelix
Super User
Super User

Hi @snandy2011,

 

Do you have any repeat values when maing the rankx? meaning that instead of having 1, 2, 3, 4, 5,6 you have something like 1,2,3,4,6,6?

Seems to me like you have repeated ranking that causes to reduce number of values to present.

 

Search your data if any of the top/bottom 5 have more than 1 customer for the same ranking.

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix,

 

Nope.. I don't think so.. 

 

Check out the below screenshots,

 

screenshots.PNG

 

It Starts with rank 2..I dont understand why?

 

But for profit or other measure, it shows correct rank.

 

Can you please identify what is happening?

 

Thanks for replying.

 

snandy

Hi @snandy2011,

 

Starting the rank in 2 is the reason you have only 4 numbers, does for any chance for the 21.78 do you have two rows on your dataset that are exactly the same?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix,

 

Sorry for late reply. No, I dont think so, i have two rows which have excatly same value (21.78). 

 

I did the same thing on excel also, But it shows me correct result.Please have a look of below excel result scrrenshots,

Excel bottom customer.PNG

 

Look, It shows me correct result. I miss this "Ken Heidel" Sales on Power Bi.

 

Any clue to solve this ridiculous problem?

 

Thanks,

Snandy

Hi @snandy2011,

 

I wasn't abble to make a complete test since I don't have your data model and there are some parts of your measure that are not clear to me.

 

On the  measure below, can you change the MAXX by MINXX and check if the Joni Sunderseam gets rank number 1 or 2?

 

Rank Selection for Bottom =
IF (
    HASONEVALUE ( 'Ranking For Bottom'[Rank] ),
    VALUES ( 'Ranking For Bottom'[Rank Number] ),
    MAXX ( VALUES ( 'Top Analysis Unpivot'[Value] ), [Rank from bottom] )
)

Since you have two values as 21.78 you are getting the higher value.

 

Can you share some sample data by private message so I can make some addtional tests?

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors