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 corre...
Need Help
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

    • paresh's avatar
      paresh
      Frequent Visitor
      10 years ago

      Thanks once again.

      However, no change in the results.

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        paresh To achieve what you want you have to remove the [Date] field your from your table

         

        even if you aggregate the data wtih YTD or a Running Total it will still be broken down by month (like in the Matrix below)

         

        the Slicers will indicate to your user what is selected - then you can select any date range(s) you want...