Hi everyone,

I have a matrix visual in Power BI with the following configuration:

Rows:

• d_customer_ranking[ranking group]

• d_customer_ranking[ranking corporate group]

• d_customer_ranking[ranking name]

Columns:

• d_Calendar[Month]

• Time Intelligence[TimeIntelligence] ← calculation group

Values:

• TopN Customers_test

Here’s my main measure:

TopN Customers_test = 
IF (
    ISINSCOPE ( d_Customer_Ranking[Ranking group] ),
    VAR NumOfCustomers = 'TopN'[TopN Value]
    VAR RankingGroup =
        SELECTEDVALUE ( d_Customer_Ranking[Ranking group] )
    VAR TopCustomers_byCorporateGroup =
        TOPN (
            NumOfCustomers,
            SUMMARIZE (
                ALLSELECTED ( 'd_Customer_Ranking' ),
                'd_Customer_Ranking'[Ranking Corporate Group],
                "CurrentBaseValue",
                    CALCULATE (
                        TOTALYTD ( [Current (base)], d_Calendar[Date] ),
                        REMOVEFILTERS ( 'Time Inteligence' )
                    )
            ),
            [CurrentBaseValue]
        )
    RETURN
        SWITCH (
            RankingGroup,
            "Best Customers", 
                CALCULATE ( [Current (base)], KEEPFILTERS ( TopCustomers_byCorporateGroup ) ),
            "Others",
                IF (
                    NOT ISINSCOPE ( d_Customer_Ranking[Ranking name] ),
                    VAR TopAmount =
                        CALCULATE (
                            [Current (base)],
                            REMOVEFILTERS ( d_Customer_Ranking[Ranking group] ),
                            TopCustomers_byCorporateGroup
                        )
                    VAR AllAmount =
                        CALCULATE ( [Current (base)], ALLSELECTED ( d_Customer_Ranking ) )
                    VAR OtherAmt = AllAmount - TopAmount
                    RETURN
                        OtherAmt
                )
        ),
    [Current (base)]
)

These are the Calculation Group measures:

PY YTD

 CALCULATE( TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]), SAMEPERIODLASTYEAR(d_Calendar[Date]) ) 

Contribution PY YTD

DIVIDE(
    CALCULATE(
        TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]),
        SAMEPERIODLASTYEAR(d_Calendar[Date])
    ),
    CALCULATE(
        TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]),
        SAMEPERIODLASTYEAR(d_Calendar[Date]),
        ALL(d_Customer_Ranking)
    )
)

Actual YTD

 TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]) 

Contribution YTD

DIVIDE(
    TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]),
    CALCULATE(
        TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]),
        ALL(d_Customer_Ranking)
    )
)

YoY Growth YTD

VAR CurYTD =
    TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date])

VAR PrevYTD =
    CALCULATE(
        TOTALYTD(SELECTEDMEASURE(), d_Calendar[Date]),
        SAMEPERIODLASTYEAR(d_Calendar[Date])
    )

RETURN
    DIVIDE(CurYTD - PrevYTD, PrevYTD)

The problem is that the Top 20 customers shown in the matrix vary depending on the calculation group selected (e.g., PY YTD, Actual YTD). As a result, the Top 20 for Actual YTD are not the same as the Top 20 for PY YTD.

The order and the number of customers showing should remain consistent and based on the current year YTD value.

In other words, I need a way to decouple the TopN definition from the calculation group context.

Any suggestions on how to "freeze" the TopN list to the YTD ranking?

Thanks in advance for any ideas!