cancel
Showing results 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

Post Patron

Bottom 5 Sales by Market Measure

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!

1 ACCEPTED SOLUTION
Super User

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.

9 REPLIES 9
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Patron

Apologies, I missed this message.

Bottom 5 Sales by Market =
VAR MarketRank = RANKX( ALL( 'Sales Table'[Market]) , [Total Sales] , , ASC)
RETURN
CALCULATE(
[Total Sales],
FILTER(
'Sales Table',
MarketRank <= 5
)
)
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Post Patron

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

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

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Post Patron

Hi Rita,

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

Super User

I can't know without seeing the pbix.

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.

Super User

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