cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StefanM
Helper II
Helper II

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 top 5 categories per date. But if it's higher, on say week or month, then it shows the top 5 for each week or month instead. 

 

 Date/Week/Month 1 has a different top 5 from Date/Week/Month 2. But the problem I'm encountering is in filtering by Top N which filters to only show me the Top 5 for the entire range, so only 5 categories show in total, even though they are not the top 5 for those specific ranges. 

To give an idea of what I'm aiming for here, here's an image of an example table showing off the raw data (left) to the final table (right) using some common sense working out. 

 

From Raw Data to Final TableFrom Raw Data to Final Table

 

In actuality, there are potentially 100s of Categories and the list is ever-growing. So it needs to be something dynamic. I've been wrecking my head on this for a few days and can't figure it out.

Any wizards out there who can help?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 🙂

View solution in original post

4 REPLIES 4
StefanM
Helper II
Helper II

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:
example 2.png

 

Anonymous
Not applicable

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 🙂

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. 

example 3.png

 

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
Not applicable

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors