Ranking categories but ignoring subcategory

[Diegomon]

Hello community,

I am having a problem trying to achieve the following ranking (based on Value column) by using DAX, is there any solution for it?

[v-henryk-mstf · Accepted solution]

Hi @Diegomon ,

The formula can be modified to retain the date filter conditions corresponding to the external filter context.

Try like  below:

M_ = 
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    ALLEXCEPT (
        DimProductSubcategory,
        DimProductCategory[EnglishProductCategoryName]
    ),
    ALLEXCEPT ( DimDate, DimDate[FiscalYear] )
)

If the problem is still not resolved, please provide detailed error information and text data. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jos_Woolley]

Hi,

MyRank =
RANKX(
    ALLSELECTED( 'Table' ),
    CALCULATE( SUM( 'Table'[Value] ), ALLEXCEPT( 'Table', 'Table'[Category] ) ),
    ,
    ,
    DENSE
)

Regards

[Diegomon]

That's working thank you, but what happens when there are slicers filtering the data? Let's say that the table contains information about 2 years and I use the slicer to filter the information for 1 year, how can I handle that? "ALLEXCEPT" function is affecting these filters

For example, "Hospitality" should be number 1 because of year filter