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

       

       

      • paresh's avatar
        paresh
        Frequent Visitor
        10 years ago

        Thanks Sean.

         

        However, my final visualization is going to be a Box and whisker chart, showing my Top 10 Items. Now the chart requires the data in the form of a Pivot table. To get the data in that format, I have the item on 1 axis and Month on the other, with the quantities being sold.

         

        As soon as I include the month, it starts giving me the Top 10 items for each month and thus more than 10 items.

         

        How can I come out of this situation?