Forum Discussion

MoonLightFlower's avatar
MoonLightFlower
Regular Visitor
2 years ago

Need Help for a matrix with multiples Rankx

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      MoonLightFlower
      Regular Visitor
      2 years ago

      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.