How to lock TopN results in a matrix regardless of calculation group selection

[SCTomas92]

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!

[v-nmadadi-msft]

Hi @SCTomas92 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[tayloramy]

Hi @SCTomas92,

You’re running into a classic side-effect of calculation groups: they rewrite the measure result via SELECTEDMEASURE(), so your TOPN set keeps getting re-ranked under each calc item (Actual YTD, PY YTD, Contribution, etc.). The fix is to build your TopN list from a “safe” anchor measure that always evaluates Current-Year YTD and explicitly ignores the calculation group - then use that TopN list only as a filter, while still letting the selected calc item format the values you show.

Below is some (quickly written and untested, but should give you the gist) code to try

 

1. Create an anchor measure that locks the ranking to Current-Year YTD and neutralizes the calc group:

   Anchor YTD (base) =
   CALCULATE(
       TOTALYTD( [Current (base)], 'd_Calendar'[Date] ),
       REMOVEFILTERS ( 'Time Intelligence' )   -- disables the calc group inside this expression
   )

2. Use that anchor to build (virtually) the TopN customers, then apply it via KEEPFILTERS(...).
   This freezes who is in the TopN, regardless of the selected calc item:

   TopN Customers (locked) =
   VAR NumOfCustomers = SELECTEDVALUE('TopN'[TopN Value], 20)
   VAR RankingGroup    = SELECTEDVALUE( d_Customer_Ranking[Ranking group] )
   
   -- Build the TopN set using the ANCHOR measure (ignores calc group)
   VAR TopCustomers_byCorporateGroup =
       TOPN(
           NumOfCustomers,
           SUMMARIZE(
               ALLSELECTED(
                   d_Customer_Ranking[Ranking Corporate Group],
                   d_Customer_Ranking[Ranking name]
               ),
               d_Customer_Ranking[Ranking Corporate Group],
               d_Customer_Ranking[Ranking name],
               "__AnchorYTD", [Anchor YTD (base)]
           ),
           [__AnchorYTD], DESC
       )
   
   -- Totals for "Others" use the same frozen TopN set
   VAR TopAmount :=
       CALCULATE(
           [Current (base)],
           REMOVEFILTERS( d_Customer_Ranking[Ranking group] ),
           KEEPFILTERS( TopCustomers_byCorporateGroup )
       )
   VAR AllAmount :=
       CALCULATE( [Current (base)], ALLSELECTED( d_Customer_Ranking ) )
   VAR OtherAmt = AllAmount - TopAmount
   
   RETURN
   IF (
       ISINSCOPE ( d_Customer_Ranking[Ranking group] ),
       SWITCH(
           RankingGroup,
           "Best Customers",
               CALCULATE( [Current (base)], KEEPFILTERS( TopCustomers_byCorporateGroup ) ),
           "Others",
               IF( NOT ISINSCOPE( d_Customer_Ranking[Ranking name] ), OtherAmt )
       ),
       [Current (base)]
   )

3. Keep the order steady across calc items by sorting your rows by a rank measure that also uses the anchor:

   Anchor YTD Rank =
   VAR R =
       RANKX(
           ALLSELECTED(
               d_Customer_Ranking[Ranking Corporate Group],
               d_Customer_Ranking[Ranking name]
           ),
           [Anchor YTD (base)],
           , DESC, DENSE
       )
   RETURN R

   In the matrix’s “Sort by” pick Anchor YTD Rank (descending). This keeps the Top 20 and their order fixed to Current-Year YTD even when you switch to PY YTD, Contribution, YoY, etc.

why this works

• Calculation items inject themselves through SELECTEDMEASURE(). If you build the TopN with SELECTEDMEASURE() (or with a measure that a calc item modifies), your TopN will change per selection.
• REMOVEFILTERS('Time Intelligence') inside your anchor expressions prevents the calc group from applying in that scope. That gives you a stable ranking basis (Current-Year YTD).
• You then use the resulting TopN table only as a filter (KEEPFILTERS( TopCustomers_byCorporateGroup )). The displayed value can still be your normal measure, which the calc group can transform (PY, Contribution, YoY, etc.). The set of customers doesn’t change — just the numbers shown for them.
• Using ALLSELECTED(...) in the TopN table keeps slicers (except the calc group) honored. If you want the TopN to ignore cross-highlighting too, switch to ALL(...).

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[SCTomas92]

Using REMOVEFILTERS ( 'Time Inteligence' ) with TopN doesn't work for me. I've tried that in my main measure but the Calculation Group still applies. I also tried creating the Rank for the top 20 customers and then modify the calculation groups so they don't apply to the rank:

if(
isselectedmeasure("rank measure"), selectedmeasure(),
--otherwise, the calculation group measure)

but when filtering with this rank measure, the matrix breaks and shows lots of customers that shouldn't.

[tayloramy]

Hi @SCTomas92, 

Can you disable time intelligence and build a proper date table instead? TIme intelligence is usually more hastle than it's worth.