Forum Discussion

Amit92's avatar
Amit92
Regular Visitor
4 months ago
Solved

Dynamic Rank Calculation

Hi Team I have a model where i have Dim Market, Dim Brand and Fact Sales. Market ID of Dim Market is connected to Market ID of Fact Sales Brand ID of Dim Brand is connected to Brand ID of Fact S...
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

    Please try the measure below:

    Kingfisher Top 5 Markets =
COUNTROWS (
    FILTER (
        VALUES ( 'Dim Market'[MarketID] ),
        VAR _KingfisherSales =
            CALCULATE (
                [Sales],
                'Dim Brand'[Brand] = "Kingfisher"
            )
        VAR _Rank =
            RANKX (
                ALLSELECTED ( 'Dim Brand'[Brand] ),
                CALCULATE ( [Sales] ),
                _KingfisherSales,
                DESC,
                Dense
            )
        RETURN
            _Rank <= 5
    )
)
johnt75's avatar
johnt75
Super User
4 months ago

I think you can use

Num markets Kingfisher Top 5 =
VAR MarketsAndBrands =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE ( Sales, Market[Market], Brand[Name] ),
            ALLSELECTED ()
        ),
        "@sales", [Sales]
    )
VAR Top5 =
    WINDOW (
        1,
        ABS,
        5,
        ABS,
        MarketsAndBrands,
        ORDERBY ( [@sales], DESC ),
        PARTITIONBY ( Market[Market] )
    )
VAR Result =
    COUNTROWS ( FILTER ( Top5, Brand[Name] = "Kingfisher" ) )
RETURN
    Result
  • Amit92's avatar
    Amit92
    Regular Visitor
    4 months ago

    Hi John,

    Thanks for the quick help,
    I am getting error in the above measure that the column Market[Market] specified in the 'SUMMARIZE' function was not found in the input table

     

    • johnt75's avatar
      johnt75
      Super User
      4 months ago

      Change both references to Market[Market] to whichever column from your market dimension uniquely identifies a row. e.g. you could use Market[Market ID].