Top N and others in calculation group

[Nikolainoergard]

Hi community

I have followed this great material by SQLBI to do a dynamic TopN & Others solution : https://www.sqlbi.com/tv/implementing-the-top-n-and-others-pattern-using-calculation-groups-in-dax-u...

I have a issue with incorrect Ranking. I can't figure out why.. Any ideas what to do? In some filter cases it return correct ranking, but mostly incorrect.

Relevant measures below:

Ranking = 
IF (
    ISINSCOPE ( 'Customer Names'[Customer_Name] ),
    VAR CustomersToRank = [TopN Value]
    VAR MeasureForRanking = SELECTEDVALUE ( 'Ranking measure'[Measure to use] )
    VAR Val = [ValueForRanking]
    VAR IsOtherSelected = SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others"
    RETURN
        IF (
            IsOtherSelected,
            -- Rank for Others
            CustomersToRank + 1,
            
            -- Rank for regular Customers
            IF (
                Val > 0,
                VAR VisibleCustomers =
                    CALCULATETABLE (
                        VALUES ( 'pbi DimCustomer' ),
                        ALLSELECTED ( 'Customer Names' )
                    )
                VAR Ranking =
                    RANKX ( VisibleCustomers, [ValueForRanking], Val )
                RETURN
                    IF ( Ranking > 0 && Ranking <= CustomersToRank, Ranking )
            )
        )
)

ValueForRanking = 
VAR MeasureForRanking =
    SELECTEDVALUE ( 'Ranking measure'[Measure to use] )
VAR Val =
    SWITCH (
        TRUE,
        MeasureForRanking = "Sales", [Sales],
        MeasureForRanking = "Order intake", [Order intake],
        MeasureForRanking = "Order bank", [Order bank],
        [Sales]
    )
RETURN
    Val

Calculation item:

IF (
    NOT ISSELECTEDMEASURE ( [Ranking] ),
    VAR ValueOfAll =
        CALCULATE ( SELECTEDMEASURE (), REMOVEFILTERS ( 'Customer Names' ) )
    RETURN
        IF (
            NOT ISINSCOPE ( 'Customer Names'[Customer_Name] ),
            -- Calculation for a group of products
            ValueOfAll,
            -- Calculation for one product name
            VAR CustomersToRank = [TopN Value]
            VAR ValueOfCurrentCustomer =
                SELECTEDMEASURE ()
            VAR IsOtherSelected =
                SELECTEDVALUE ( 'Customer Names'[Customer_Name] ) = "Others"
            RETURN
                IF (
                    NOT IsOtherSelected,
                    -- Calculation for a regular product
                    VAR Ranking = [Ranking]
                    VAR Result =
                        IF (
                            NOT ISBLANK ( Ranking ),
                            ( Ranking <= CustomersToRank ) - ( Ranking = CustomersToRank + 1 )
                        )
                    RETURN
                        IF ( Result, ValueOfCurrentCustomer ),
                    -- Calculation for Others
                    VAR VisibleCustomers =
                        CALCULATETABLE (
                            VALUES ( 'pbi DimCustomer' ),
                            ALLSELECTED ( 'Customer Names'[Customer_Name] )
                        )
                    VAR CustomersWithValues =
                        ADDCOLUMNS ( 
                            VisibleCustomers, 
                            "@ValForRanking", [ValueForRanking],
                            "@SelMeasureAmount", SELECTEDMEASURE ()
                        )
                    VAR ValueOfTopCustomers =
                        SUMX (
                            TOPN ( CustomersToRank, CustomersWithValues, [@ValForRanking] ),
                            [@SelMeasureAmount]
                        )
                    VAR ValueOfOthers = ValueOfAll - ValueOfTopCustomers
                    RETURN
                        ValueOfOthers
                )
        ),
    SELECTEDMEASURE ()
)

Would appreicate any ideas/help to fix this

Thanks

[Nikolainoergard]

Bump. Please let me know if more information is needed for understanding context.

[Anonymous]

Hi @Nikolainoergard ,

Based on the description, please try to confirm the filter applied isn’t affecting the ranking measure.

Then, review the Ranking measure formula. Try to use the all dax in the VisibleCustomers variable.

IF (
                Val > 0,
                VAR VisibleCustomers =
                    CALCULATETABLE (
                        VALUES ( 'pbi DimCustomer' ),
                        ALL( 'Customer Names' )
                    )
                VAR Ranking =
                    RANKX ( VisibleCustomers, [ValueForRanking], Val )
                RETURN
                    IF ( Ranking > 0 && Ranking <= CustomersToRank, Ranking )
            )

RANKX function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nikolainoergard]

Hi @Anonymous 

Thanks for your message

I have tried diffrent options regarding applied filters.
1) Removing Calc. item currency conversion impact (Using hardcoded measure)

2) Removing Date filter 

3) Using ALL instead of AALSELECTED in Ranking measure

..And unfortunately i still get wrong ranking