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 Sales
We have a brand named "Kingfisher" in the Dim Brand

I need to create a measure with below logic:

Based on the slicer selection, I want the count of markets where the brand "Kingfisher" is coming on top 5 for a measure named Sales

In Short: 

Count the number of markets where “Kingfisher” appears in the Top 5 brands by Sales, respecting the current filter context.

Thanks
Amit
  • 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
    )
)

4 Replies

  • 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].

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    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
    )
)