Dynamic Rank Calculation

[Amit92]

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 · Accepted solution]

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]

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]

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]

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