Here is a possible solution.
-I make use of multiple axis. Each axis have its own set of shelves so you can set properties on your measures independently. (i am glad to see that now in 8.2 when setting a dual-axis , it no more brings the Measure Names on the color shelf of all axis)
-I bring both month in the view (needed for computation) but using the last() function I filter to show only the latest.
-To Sort based on the current month sales, i bring this value on the rows shelf (unselect Show Header) but i return either the positive or the negative value depending on the Sort Order parameter.
-Finally, I use the index() and last() function along with the Top N parameter to keep only the Top N values in the view.
thanks a lot for your help. It's exactly what I was looking for :-)
It just seems that there is a problem with the Top N.
If you remove the Top N filter from the visualization you can see that the sub-category "Binders and Binder Accessories" is at the rank 13 with $6409 in May. But when you add the Filter Top N, the group "Binders and Binder Accessories" always appears at the last position when the Top N is between 7 and 13. So it looks that the Top N is not 100% correct.
Do you know where this issue might be coming from?
Thanks again for your help.
You are right Amandine, the Sorting does not work. What i see is because both month are in the Details, the index functions (last,first,index) are computed based on the Sum of the Sales for both month. And since binders and Binders had 40k in April the are always in the Top 7.
I haven`t found a rapid solution, but i don`t have much time to investigate for now. I`ll check later.
Maybe someone else will jump in and find a solution to this interesting problem.
Check in the attached, the sorting should be ok now. I think i overcomplicated things in my first try.
Also i made two filters. One will keep the Top sales and Sort them asc or desc, and the second will give you the Top sales when selecting the desc order , and the Bottom sales when selecting the asc order.
thank you very much for having looked into it one more time.
It works perfectly fine now. That's great.
Many thanks for your help,