cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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

table shops3.png

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 at the same time filtering by 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

Re: Filter context issue trying to visualize top ranked items in different categories

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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Filter context issue trying to visualize top ranked items in different categories

Highlighted
Solution Specialist
Solution Specialist

Re: Filter context issue trying to visualize top ranked items in different categories

It's ugly, but some might find its simplicity beautiful. Just duplicate your sales table and display a separate chart from the second table yet filtered on your number one article.

 

It's probably easy to understand for an outsider when they need to work with your file vs. trying to understand the DAX code required for the fancy solution. Unless of course performance prohibits this solution.

 

Does this work for you? If so, please mark as solution.

 

Kind regards,

 

Steve. 

Highlighted
Helper II
Helper II

Re: Filter context issue trying to visualize top ranked items in different categories

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

View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Re: Filter context issue trying to visualize top ranked items in different categories

Nice! Thats a more elegant way to implement. 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors