Getting TOP n Items in a Table/chart

[paresh]

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

[Sean]

@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]

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]

@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]

Thanks once again.

However, no change in the results.

[Sean]

@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]

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? 

[v-yuezhe-msft]

Hi paresh,

Regarding to the issue that how to display data properly in Box and whisker chart, please directly contact the author. In this link, you can send an email to the author by clicking “Contact Author” as shown in the following screenshot.

Thanks,
Lydia Zhang

[paresh]

Thanks Lydia.

However, I dont think it is an issue with this specific visualization. If I am able to get the correct data in a Table visualization, it will also show correctly in the Box and Whisker chart.

So I feel, it is primarily to do with formulating the correct expressions to get the correct values. Rest will all fall in place.

[paresh]

I have tried to make some data in Excel and linked that to a PowerPivot (I have never worked in PowerPivot and will finally want my results in PowerBI, but just to simplify things)

The excel Sheet can be downloaded from this Link

I have also explained in the 2nd sheet what I want.

[paresh]

I will try and explain my problem a litte more.

I want to pick my Top 3 items and then graph them.

So the RankX measure has been defined.

If I select my month Filter as 1, I get the following results

 

So Coke is at Rank 4 and Pepsi is at 1

If I change the Month to 2

 

Now for this month, Coke moves to the 2nd position.

However If I select both the months, I want a consolidated ranking of my items. Whereas I get the rankings monthwise

 

In the output that I want, the Rank column for the same item should be the same. The ranking for Coke should be 2, Fanta 4, Pepsi 1 and Sprite 3