Forum Discussion
Ranking items from multiple periods
Hello everyone,
Yet another ranking question but I seem to be stuck. Basically, I have a table consisting of a productid, year, month and sales. It looks like this:
As you can see each product entry is repeated since there are 12 months.
My goal is to visualise these products in a Bar Chart with the ability to drill down from Year to Month; and to display only the top 10 products. From my understanding that could be done by ranking the products and then filtering them, hence I started with the following column:
Column1 = RANKX(ALL('DATA SALEITEMS (2)'),'DATA SALEITEMS (2)'[SALES])However, it doesn't take into account that the product sales are related to different months, and ranks them overall.
I have tried using the following function, thinking that it would only take into account the 7th month
Column2 = if('DATA SALEITEMS (2)'[MONTH]=7,RANKX(ALL('DATA SALEITEMS (2)'),'DATA SALEITEMS (2)'[SALES]),blank())...but again there is an issue as the items are still ranked in the same with the only thing being different is that there are blank cells if the month is different from 7.
I would be really glad if someone can provide some assistance with that challenge.
you can try two things:
1. Create a calculated measure called Total Sales = Sum(Sales)
2. For ranking Measure use following expression:
Rank = RANKX(ALL(Table[ProductID],[Total Sales])
this will rank Each product for each month on basis of sales. You can use the new Rank measure in your visual filters and filter on topN. I think the calculated measures cannot be used as slicers so you have to use it as filter (Please someone correct me if I am wrong)
Regards
Harris
13 Replies
HarrisMalikContinued Contributor
you can try two things:
1. Create a calculated measure called Total Sales = Sum(Sales)
2. For ranking Measure use following expression:
Rank = RANKX(ALL(Table[ProductID],[Total Sales])
this will rank Each product for each month on basis of sales. You can use the new Rank measure in your visual filters and filter on topN. I think the calculated measures cannot be used as slicers so you have to use it as filter (Please someone correct me if I am wrong)
Regards
Harris
- Bjoern
HarrisMalik You can definitely use calculated colums als slicers. I do it quite often, if I have categorized data.
I see a problem that you can not filter for top10 in visuals, but only apply a "sort by". You have to filter based on the rank in the filter of the respective visual:
See also:
http://community.powerbi.com/t5/Desktop/Show-top-n-entries/td-p/1490
- HarrisMalik
Bjoern Ofcourse you can use calculated columns in the slicers but I am taking about calculated measures. Did you use caluclated measure as slicer?
Cheers
Harris
Hello and thank you guys!
Using the two measures provides an awesome solution to my problem.
However, I noticed that when I add my dates in the axis the "Sort by" option from the upper right corner of the chart dissapears. The result is that the top 10 items are still displayed, however they are randomised. Is there a possible way to tacke this?
Best of all.
if I don't put the "Date" in the Axis I am able to sort it by sales as in the picture:
However, when I put Date in the axis (on top of ID), the sort by dissapears.
Perhaps I should create a slicer instead of having the ability to drill down. But using the slicer only displays the year not the months.
Best,
Stan
