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
gbarr12345
Post Patron
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!

 

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...

 

 

1 ACCEPTED SOLUTION
Ritaf1983
Super User
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())
Ritaf1983_0-1715737679461.png

 

pbix is attached
 

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

 

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

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
    )
)

Hi,

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

Ashish_Mathur_0-1715860728079.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ritaf1983
Super User
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())
Ritaf1983_0-1715737679461.png

 

pbix is attached
 

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

 

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?

 

gbarr12345_0-1715738083560.png

 

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
Super User
Super User

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

lbendlin_0-1715737374480.png

lbendlin_1-1715737420646.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors