How can I remove filters from a RANKX function in powerbi?

[Anonymous]

I've been struggling with removing filters from the rank calculation in dax.

I have a sample table with units per store for individual items broken out by their respective Department, Category, Sub-Category, Parent Company, and Parent Brand.

     testrank = RANKX(ALLSELECTED(Sales), CALCULATE(SUM(Sales[UnitsPerStore])),,ASC)

As you can see from the above, the rank is working how I would expect. However, I want the end user to be able to filter to a particular brand they are curious about without affecting the rank. Below I filtered to ParentBrand of Carvel. The rank automatically changed based on the visual:

So the only filters that the end user applies that I want to affect the calculation are Department, Category, and Subcategory. So if someone looks up a particular UPC for example, they would see the rank of that upc based on the selection of the department/category/subcategory.

I have tested a couple methods and feel as if I"m getting close:

method1 = RANKX(FILTER(ALL(Sales), Sales[CorporateDepartment] = max(Sales[CorporateDepartment]) && Sales[Category] = max(Sales[Category]) && Sales[SubCategory]=max(Sales[SubCategory])), CALCULATE(MAX(Sales[UnitsPerStore])),, ASC,Dense)

method2 = RANKX(ALLEXCEPT(Sales, Sales[CorporateDepartment], Sales[Category], Sales[SubCategory]), CALCULATE(Sum(Sales[UnitsPerStore])),,ASC,Dense)

So with both Methods, the filtering issue was resolved. If I filtered to a brand, it wouldn't affect the original ranking the item had based on what category or subcategory I had selected. However for some reason the rankings are off. I'm seeing repeat rankings, almost as if the rank has a group by applied to it. 

Would apreciate any help!

[VahidDM]

HI @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/

[Anonymous]

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!

[AlexisOlson]

It is doing a sort of grouping. From this documentation:

"When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments. In this case, the result only has the columns of the table and ignores the expanded table."

Maybe if you use it not as a table argument, then it will work like you expect:

method2 =
RANKX (
    CALCULATETABLE (
        Sales,
        ALLEXCEPT (
            Sales,
            Sales[CorporateDepartment],
            Sales[Category],
            Sales[SubCategory]
        )
    ),
    CALCULATE ( SUM ( Sales[UnitsPerStore] ) ),
    ,
    ASC,
    DENSE
)

[Anonymous]

Thank you for your quick response! I think we are still having the same issue. I named your suggestion as "Method3" in the below table. It looks like the ranking is following exactly what Method 1 was doing:

[AlexisOlson · Accepted solution]

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.

[Anonymous]

I will try that out! As far as the dimension table goes, would it just be a 3 column table of all the possible combinations of Department, Category, and Subcategory? Or would it be a dimension table with all the item info available (UPC, Department, Category, SubCategory, ParentBrand, and Parent Comapny)?

[mdarlington]

I was wondering if you found a solution to this issue.  I am having the exact same problem.

[AlexisOlson]

Generally, you do want that sort of stuff in a dimension table rather than a fact table. If you're going to create a dimension table anyway, might as well put all of the relevant columns there.