Forum Discussion

StefanM's avatar
StefanM
Icon for Helper II rankHelper II
6 years ago
Solved

Top N per date in a chart

Hello PBI community! I'm trying to show the most common categories based on a charts drilled down selection. So if it's drilled all the way down to individual dates, then the chart displays the to...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    I think you are looking for this:

     

    Calculated column: 

     

    Rank =
    RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Quantity])
     
    At least that took me from your table 2 to table 3 🙂
StefanM's avatar
StefanM
Icon for Helper II rankHelper II
6 years ago

I've made the middle table in the image, now I just need to rank them to prevent the items beyond the top 3 from appearing. 
How can I go about that? I've tried so many measures, but nothing is working.  

Image below for example:

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I think you are looking for this:

 

Calculated column: 

 

Rank =
RANKX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),'Table'[Quantity])
 
At least that took me from your table 2 to table 3 🙂
  • StefanM's avatar
    StefanM
    Icon for Helper II rankHelper II
    6 years ago

    Ohh, this is nice! Works very well. Every category available on each date is now ranked. 

    Now I've a new problem, the ranking doesn't stay true to the date selection on the chart. 

     

    As seen in the above image, the bottom left chart is filtered down to Day, in which the top 3 is shown as expected. But in the top chart that's drilled up to Monthly Data, the top 3 for each day is shown on that month, rather than the top 3 per month... 

    I'm thinking that I'm perhaps going about this wrong S: any ideas?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hey Stefan,

       

      I think you'll end up with something along these lines:

       

      MEASURE, not a column this time 🙂

       

      RankMeasure =
      IF(HASONEVALUE('Table'[Month]),
      RANKX(ALL('Table'[Month]),
      CALCULATE(
      SUM('Table'[Qty])
      )
      ),
      IF(HASONEVALUE('Table'[Date]),
      RANKX(ALL('Table'[Date]),
      CALCULATE(
      SUM('Table'[Qty])
      ))))

      You basically add an if statement for every level of granularity you need (year, month, quarter, date). 
      There is probably a prettier way of doing this, but it seems to work for me 🙂
       
      Jaap

      This article is a great help, rankX is a really hard to understand DAX function!  https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures