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
    )
)
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
    )
)