Bottom 5 customers for profit but sales should be greater than 100K

[Anonymous]

Hi Community,

I have to find lowest customers profit and i have a condition that sales should be greathan 100k.

Bottom 5 customers for profit but sales should be greater than 100K.

Please help.

Thanks,

Paruchuri

[v-chuncz-msft]

@Anonymous ,

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/RANKX-excluding-blanks/m-p/397656#M181491

[Anonymous]

@v-chuncz-msft  It doesn't give me the expected result. I would like to create the measure first it should check the sales >100 and from that it would give the bottom 5 Profit. 

I have created the customer rank formula as 

Customer Rank =

IF (

([Sales >100K]>100),

RANKX (

FILTER ( ALLSELECTED ( [CustName] ), ( [Sales >100K] >100) ),

[Sales >100K]

)

)

I would need like below.

INPUT

Thanks/

[parry2k]

@Anonymous this post will help, but if you still cannot figure out, please share data in excel using onedrive/google drive to get your answer quickly. Remove any sensitive information before sharing.

[Anonymous]

I tried it but not working. I have pasted the excel sheet.

https://1drv.ms/f/s!AjL_xzhqwRM-wxss_RQr9e1vMspj

[parry2k · Accepted solution]

@Anonymous add new rank column as below and then you can filter on this column

Rank = IF( [Sales] > 100, RANKX( FILTER( Table1, Table1[Sales] > 100 ), Table1[Profit %], , ASC,Dense ) )

Here is the output 

[Anonymous]

The formula is working fine but i'm getting only 1,2 ranks. 

[parry2k]

@Anonymous if you see my output it is working as expected, I'm not sure what is your dataset, are you using measures etc etc

[Anonymous]

The sales and profits are measures. 

[parry2k]

@Anonymous add my DAX expression as measure instead of column as I suggested before, 

[Anonymous]

i created measure only.