Forum Discussion
Helper IIIFilter context issue trying to visualize top ranked items in different categories
Hi,
I'm working with a dataset containing the subcategories Area, Product and Article for different Shops, and the amount of sales by Article. For each shop the ranking of Area is determined according to the total sales. Then for the Area ranked 1 the ranking of Products is found, and for the Product ranked 1 the ranking of the Articles is in turn determined.
So, for each Shop, I have the top Article within the top Product subcategory within the top Area of that Shop. Three shops, three top articles.
Now I need to compare the sales of each of these three top articles in all the shops in a bar chart, switching the top Article by means of the Shop slicer already existent in the canvas: selecting Shop A the chart must show the sales of the top Article in Shop A in all three shops, and so on (shops in x-axis).
My problem is with the Shop filter. In the measure I need to get the sales in all shops but considering the selected shop in order to set the article. I tested this measure but it doesn't work:
NSalesBarChart =
VAR
Rank1Article = CALCULATE(
SELECTEDVALUE(Sales[Article]),
FILTER(VALUES(Sales[Article]), [Rank Article] = 1)
)
RETURN
CALCULATE(MIN(Sales[ArticleNSales]), Sales[Article] = Rank1Article)Ideas? Find below the link to the pbix file. Thanks!
https://drive.google.com/file/d/1eLWKETSCFSU0inPrFfVpyi2dI-DZ7tzj/view?usp=sharing
2 Replies
- amitchandak
Super User
arielcedola ,For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415 
arielcedolaSolved. I modified the measure as follows:
NSalesBarChart = VAR CurrentShop = CALCULATE(SELECTEDVALUE(TabellaShop[Shop])) VAR Rank1Article = CALCULATE( SELECTEDVALUE(Sales[Article]), FILTER(VALUES(Sales[Article]), [Rank Article] = 1) ) RETURN CALCULATE(MIN(Sales[ArticleNSales]), FILTER(ALL(Sales), Sales[Article] = Rank1Article && Sales[Shop] = currentShop))I created an auxiliary table with the names of the shops (TabellaShop) and used these values on the x-axis of the bar chart. I filter the shop using this as a way to ignore the Shop filter context, applied rather to the selection of the top ranked Article to visualize.
Best,
Ariel
