The problem would appear to be simple, but the solution very hard.
I have a table (myTable) with 4 columns: Vendor, Amount, Date, and Year (which is simply the year extracted from the date);
It should be easy to sum the amounts for each vendor and display the top 10 vendors in a chart or table, correct?
I can create a column: "Sum of Amount" = SUM(myTable[Amount])
I can create a column: "Rank of Amount" = RANKX(ALLSELECTED(myTable[Vendor]),[Sum of Amount])
However, when the slicer on Year is put on the report and MORE THAN ONE YEAR is selected, the result is whacky. If Vendor A is ranked 1st in year 1, and vendor B is ranked 1st in year 2, and vendor C is ranked 1st over both years, then I get three vendors ranked as 1st!!!! WTF? Slicing should reduce results, not add to them! If I did no slicing at all, I get only 1 result which is Vendor C ranked as 1st... Vendor C is the correct answer, not Vendor A or Vendor B over the two years selected in the slicer. Since both year 1 and year 2 were selected, all the records for Year 1 and Year 2 are part of the result set and the vendor with the highest amount should be ranked 1st.
So, what do I do to fix this and expand the fix / concept to include a lot more slicers? Thoughts and theory?
Or, am I not explaining the problem very well?
Microsoft, please oh please put a TOP 10 type function on the charts. Also, we need an option to hide all unused columns (columns not used in a report / chart).
Solved! Go to Solution.
Thank you for your reply. I do find that this is a very complicated proceedure for a very common problem. I hope Microsoft gets on the ball and provides simple filters to only show the TOPx (or BOTTOMx) number of results. This appears very, very complicated, especially when relationships get involved. When the example above includes a related calendar table, then it is bang head against wall time. At that point I seem to need to use the calendar table to get my results, but then I also have to filter the calendar table slicers to only show what is relevant and AHHHHHhhhh. I just want the top 10, dang it!
In that case, the example you sent me to doesn't seem to work and appears to have the same problem.
I find it is easier simply "squeezing" the charts to only show the TOPx in a table. Graphical charts are just a royal pain in the a@@ to limit the results to only the TOPx.
A bit late as you tried the hard way. I agree that we must have an option to filter like pivots ( only topN ) but there is a workaround.
Actually RANKX is a bit difficult due to filter & row context. First yes you will need a different date table for ALLSELECTED to work.
Your problem occurs I suppose due to your measure being implicit & don't use row context. Need to wrap it with CALCULATE
Try this : RANKX(ALLSELECTED(Table[Vendor]);CALCULATE(SUM(Table[Amount])))
If you don't want the rankto be shown in graph but only filter then try to add the measure only in visual filters on the selected graph and apply a "less or equal". see screenshot
Hope that helps
I like that answer as being more concise to give me the same results.
I will say that in reality, I haven't found a solution that works. I can't rank the results properly. I marked the answers correct, because the answer probably works for somebody, but not for me.
Here are two screenshots. I had to take two in order to protect confidentiality of the data. You can see, it simply doesn't work. When I select two (2) years, then I am hosed. It works for none or one (1) year. For two years, I get 3 number 3s. One of those number 3s is ranked 3rd in 2014, the other in 2015, and the other overall for both 2014 and 2015.
Oh well. Such is life.
Thanks for marking as an answer my comments.
I tried to reproduce a more detailed dataset close to yours but it worked..
The only notice which I cannot see from the screenshots ( seems like you have only one table ) is that you must use slicer from another date table and not from the same one..(already mention it so probably you applied it). Because also in my case it didn't work with slicer from the same table.( due to ALLSELECTED -> remove the latest context)
If you need a date table for only Year slicer then just create a table in one excel with the years and load it, establish the relantionship to see if it works.
Also try to create an explicit measure for amount Total Amount = SUM(Pomast[Amount]) and use that in the table/graph
Unfortunately without seeing the actual data I cannot do anything more.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.