Need Help for a matrix with multiples Rankx

[MoonLightFlower]

Dear BI community,
I need your help about  a matrix with multiple Rankx :

What i am trying to do is a matrix
with the rows :

• country
• Category (Take Top 10 Category with the higher SalesAmount)
• SubCategory(Take Top 5Category with the higher Order Qty and only in the top 10 category)
• Product (Take Top 5 product with the highest Order Qty, but it should be in the Top 5 sub category )
  Just a little remind the Top Category is ranked with the the Sales Amount
  and the Top SubCategory and Product use the  Order Qty

Measure :

• Sum(AmountSales)
• Sum(Qty)

For the sample i used the adventure Works DW 2022 :

At first i plan to do 3 Rankx :

RankxCategory =
RANKX(ALLSELECTED(DimProductCategory[EnglishProductCategoryName]),CALCULATE(SUM(FactResellerSales[SalesAmount]),ALLSELECTED(DimProductSubcategory[EnglishProductSubcategoryName]
,DimProduct[EnglishProductName]
)),,DESC,Dense)

RankxSubCategory =
RANKX(ALLSELECTED(DimProductSubcategory[EnglishProductSubcategoryName]),CALCULATE(SUM(FactResellerSales[OrderQuantity])
,ALLSELECTED(DimProduct[EnglishProductName])
),,DESC,Dense)

RankxProduct =
RANKX(ALLSELECTED(DimProduct[EnglishProductName]),CALCULATE(SUM(FactResellerSales[OrderQuantit])))

and create some measure with if  :
TopNSalesAmout = IF(RankxCategory<10 && RankxSubCategory<5 && RankxProduct<5,SUM(FactResellerSales[SalesAmount])

TopNQty = IF(RankxCategory<10 && RankxSubCategory<5 && RankxProduct<5,,SUM(FactResellerSales[SalesAmount])

But i don't think it's the best solution. 
if anyone has some idea 🙂 . 

[Anonymous]

Hi  @MoonLightFlower ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Sum_Category =
 SUMX(
    FILTER(ALL('Table'),
    [country]=EARLIER('Table'[country])&&'Table'[Category]=EARLIER('Table'[Category])),
    [AmountSales])

Sum_SubCategory =
 SUMX(
    FILTER(ALL('Table'),   [country]=EARLIER('Table'[country])&&'Table'[Category]=EARLIER('Table'[Category])&&'Table'[SubCategory]=EARLIER('Table'[SubCategory])),
    [Qty])

2. Create measure.

This is a rule I created myself, and you can change the functions in it to the following form.

FILTER(
    _table1,
    [Rank_Category] <=10 && [Rank_SubCategory]<=5&&[Rank_Product]<=5)

Flag =
VAR _table1 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Rank_Category",
            RANKX (
                FILTER ( ALL ( 'Table' ), [country] = EARLIER ( 'Table'[country] ) ),
                [Sum_Category],
                ,
                DESC,
                DENSE
            ),
        "Rank_SubCategory",
            RANKX (
                FILTER (
                    ALL ( 'Table' ),
                    [country] = EARLIER ( 'Table'[country] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                ),
                [Sum_SubCategory],
                ,
                DESC,
                DENSE
            ),
        "Rank_Product",
            RANKX (
                FILTER (
                    ALL ( 'Table' ),
                    [country] = EARLIER ( 'Table'[country] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[SubCategory] = EARLIER ( 'Table'[SubCategory] )
                ),
                [Qty],
                ,
                DESC,
                DENSE
            )
    )
VAR _table2 =
    FILTER (
        _table1,
        [Rank_Category] <= 1
            && [Rank_SubCategory] <= 3
            && [Rank_Product] <= 5
    )
VAR _column =
    SELECTCOLUMNS ( _table2, "test", [Product] )
RETURN
    IF ( MAX ( 'Table'[Product] ) IN _column, 1, 0 )

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[MoonLightFlower]

Thanks for your time Liu Yang,
I just have a issue with the solution :
-i want to be able to filter the country, Category, SubCategory, Product.
So if i remove the product A224, the ranking will be recalculated and i should still have 5 product.