Bottom 5 Sales by Market Measure

[gbarr12345]

Hi there,

I'm looking to create a measure to get the bottom 5 sales by Market without needing the use of slicers/ filters.

Is it possible to do this with a measure as I can do the top 5 but I'm struggling to do the bottom 5.

I have attached a sample PBIX and my sample Excel data also below.

Any help would be appreciated!

PBIX - https://drive.google.com/file/d/1k2h-CepPaYARqsROgRIkAlv60z2khZKP/view?usp=drive_link

Excel data - https://docs.google.com/spreadsheets/d/1FNQaZ4Eom6-FqzLvoCZl8t_mOPqXIOHA/edit?usp=drive_link&ouid=11...

[Ashish_Mathur]

Hi,

Yo have just shared the download link of th e PBi file without showing the problematic visual/calculation.  Build your visual and clearly show the problem there.

[gbarr12345]

Apologies, I missed this message.

Please find the PBIX file with the dax attempt here - https://drive.google.com/file/d/1euNJ7rP3m1LALVXtiR8KdFQH9TY9v_HO/view?usp=drive_link

Bottom 5 Sales by Market =

VAR MarketRank = RANKX( ALL( 'Sales Table'[Market]) , [Total Sales] , , ASC)

RETURN

CALCULATE(

    [Total Sales],

    FILTER(

        'Sales Table',

        MarketRank <= 5

    )

)

[Ashish_Mathur]

Hi,

I just dragged Market to the visual and your measure seems to work

[gbarr12345]

That worked in the end, apologies my system was just acting up.

Thanks a million for your help Ashish as per usual!!

[Ashish_Mathur]

You are welcome.

[Ritaf1983 · Accepted solution]

Hi @gbarr12345 
You can use a measure :

Bottom_5 =

Var

Rank_market = RANKX(ALLSELECTED('Dimension_Market Table'[Market]),[total_sales],,ASC,Dense)

RETURN

IF(Rank_market>0 &&Rank_market<6,[total_sales],BLANK())

pbix is attached

 

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

[gbarr12345]

Hi Rita,

Thank you for your response.

I tried your measure and it's showing as blank for me. Any idea why this is happening?

[Ritaf1983]

I can't know without seeing the pbix.
Please download the file that I attached in the first reaction and follow my steps.

If it still doesn't work, attach yours with the problem....

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

[lbendlin]

If you can to the Top 5 then you can do the Bottom 5 too - just revert the sort order.