Forum Discussion

paresh's avatar
paresh
Frequent Visitor
10 years ago

Getting TOP n Items in a Table/chart

I have gone through the forum and have found many queries which pertain to the above topic (have searched on TopN and RankX). I have tried all of them and have not been able to come up with the correct result. 

I will try and put my problem in as simple terms as I can. So here goes.

 

I am trying to create some analytics for a restaurant. I want to show the Top 10 items which were sold (by quantity) withing a given date range (dates being select from a slicer - picking years or months)

 

So I created a measure for summing up my quantities

 

Msr_SaleQty = sum('sales'[quantity])

 

Next I created another measure to get the Rank

 

Msr_Rank_by_qty = RANKX(ALL('sales'[item]),[msr_saleqty])

 

So far so good.

Now I create a table visualization and add the item, quantity and the Rank in it. ( i have just done Top 3 in the example)

 

 

This works perfectly.

However If I add a month column into this table, I get the ranking for each month. So If I have selected 2 months, then I will get 6 rows.

 

But I just want the composite ranking within whatever periods I have selected.

 

I have looked at the following article

RANKX-apalooza

Tried to change my ranking measure according to this 

Created another measure as follows

 

Msr_Rank_by_qty1 = RANKX(ALLSELECTED('sales'),CALCULATE(sum('sales'[quantity])))

Now the results are worse..

 

Need Help

10 Replies

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    paresh its really late here and I'm reading this on my iPad so I can't test it right now but...

     

    try creating a TOTALYTD Measure (or some kind of a running total) instead of just SUM

     

    so when you select February it will aggregate YTD up to and including February 

     

    (allow only single select for the Month Slicer - so think of the slicer as up to and including selected Month)

     

    and then rank the items based on this measure

     

    hope this works - let me know...

    • paresh's avatar
      paresh
      Frequent Visitor
      10 years ago

      Thanks Sean for replying (even though it late in your time zone)

       

      I cannot dictate the time period as a specific month or upto a month. It could be 2 disconnected months also or it could be a couple of days. That will be totally upto the end user.

       

      Hope you understand.

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        paresh to get only 1 rank (per item) you still need to aggregate the [quantity] for all selected dates

         

        so your measure should look something like this...  

         

        Msr_SaleQty =
CALCULATE ( SUM ( 'sales'[quantity] ), ALLSELECTED ( 'table'[date] ) )

         

        And then RANK all items based on this Measure