Forum Discussion
How can I remove filters from a RANKX function in powerbi?
I have a suspicion that you won't be able to get this to work without having department and category columns in a separate dimension table due to auto-exists eliminating rows that you want to keep.
Try creating a dimension table for those columns.
Super UserHI Anonymous
Can you share a sample of your data in a table format to be able to copy and past that to PBI?
and can you add more details or a sample of the output you are looking for?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
What I'm Looking for:
Below is what the end user would see with only filtering to the ice cream and pizza Categories, they have not done any filtering outside of that. As you can see the ranking is working as it should. Please not the ranking of the Carvel Branded items:
However, the end user might really be curious about the performance of the Carvel Brand for instance. So they want to filter to them so they are only looking at Carvel. However I want the ranking that was based on the selection of Ice Cream and Pizza Categories to remain the same. With the current rankx function the ranking automatically changes to whats being filtered:
I'd like that 1 and 2 ranking to be the 5 and 8 ranking we saw in the 1st screenshot.
UPC description CorporateDepartment Category SubCategory ParentBrand ParentCompany UnitsPerStore 4 pep pizza General Pizza thin crust ragu Mizkan America, Inc. 708 5 sausage pizza General Pizza thick crust contadina Del Monte Consumer Products 553 6 choc ice cream Frozen ice cream chocolate blue bunnys wells ice cream 485 7 red pizza sauce General Pizza Sauce Gustare Vita Gustare Vita 467 8 Caramel Delight Frozen Ice Cream Novelty Outshine Nestle Dreyers 400 9 Pizzaria Style Pizza Sauce General Pizza Sauce Prego Campbell Soup Company 388 10 Vanilla Ice Cream Frozen Ice Cream Vanilla Ben and Jerrys Ben & Jerrys 367 11 Cookies and Cream Ice Cream Frozen Ice Cream Ice Cream blue bunnys wells ice cream 337 12 cheese pizza General Pizza thin crust ragu Mizkan America, Inc. 437 Hopefully this sample data gives you what you need. Thanks for the help!