Getting bottom 3 dynamicaly

[Anonymous]

Hi, this one is a bit complicated so i'll try and explain as best as I can. 

I have a table with Order ID, Seller, Ranking of price.

Each order has a different number of rankings. E.g Order 1 might have 6 sellers so would have rankings 1-6.

I would like to calculate the bottom 3 but factor in for orders that have less than 6 rankings. E.g Order 2 has 5 rankings so my measure should only count the bottom 2 as being 'bottom 3'. Order 3 has 4 rankings so my measure should only count the bottom 1 ranking. 

Right now I have a variable that is able to calculate the number of rankings per order:

CALCULATE(MAX('Test Table'[Final Ranking]),ALLEXCEPT('Test Table','Test Table'[Order ID]))

//This would return 6 if the order had 6 sellers/rankings.

I then made another variable that did the logic for working out bottom 3 which works correctly. E.g when I return this variable and put it in a table visualisation with Order ID and final ranking, it filters for rankings that are in the bottom 3 (which the correct logic above). Here's the code for that variable:

VAR bottom3 = CALCULATE(COUNT('Test Table'[Street Order ID]),FILTER('Test Table',

SWITCH(TRUE,

    numberOfRankings==5,'Test Table'[Final Ranking]>numberOfRankings-2,

    numberOfRankings==4,'Test Table'[Final Ranking]>numberOfRankings-1,

    numberOfRankings>=6,'Test Table'[Final Ranking]>numberOfRankings-3,

    numberOfRankings==3 || numberOfRankings==2 || numberOfRankings==1,0)))

Here's a picture showing the measure working:

 

However, when I try to put the measure in a table with seller, it gives me incorrect data. 

My final goal is to have a table with seller and number of orders that the seller ranked in the bottom 3. 

[v-eachen-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information? Please upload your files to OneDrive and share the link here.

[Anonymous]

@Greg_Deckler @v-eachen-msft https://1drv.ms/u/s!AmSmgu3y1hya6Hs21_KKtUPVBeXM?e=NAk0qA - sample file.

The expected output is described above. Thank you

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could create a new measure based on the "isBottom3".

_isBottom3 =
SUMX ( ALLEXCEPT ( Example, Example[Seller] ), [isBottom3] )

Here is the result.

[Anonymous]

Great! That worked! Thank you very much

[Greg_Deckler]

Sample data please. 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

[amitchandak]

Refer:https://community.powerbi.com/t5/Desktop/TOP-and-bottom-10-over-a-3-different-measures/td-p/158466

https://www.tutorialgateway.org/power-bi-top-10-filters/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale