Dynamic The Other Category

[tracytran91]

Hi friends, 

Currently, I am working on dynamic Top N and Other Category. I wrote DAX with Top X and Other as belows: 

Top X Client = 
	VAR TopNumber =if(HASONEVALUE('TopN'[Top N]),VALUES('TopN'[Top N]),10)
    VAR Rest =
        COUNTROWS ( D_Client ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ('Client Name'[Client Name]),
            CALCULATE (
               [% Cont Client 2020],
                FILTER (
                    D_Client,
                    [Client Name] = VALUES ( 'Client Name'[Client Name] )
                        && CONTAINS (
                            TOPN (
                                TopNumber,
                                ADDCOLUMNS (
                                    ALL ( 'Client Name'[Client Name]),
                                    "Contribution", CALCULATE (
                                        [% Cont Client 2020],
                                        FILTER ( D_Client, D_Client[Client Name] = EARLIER ( [Client Name] ) )
                                    )
                                ),
                                [Contribution], DESC
                            ),
                            'Client Name'[Client Name], VALUES ('Client Name'[Client Name] )
                        )
                )
            )
        )

OTHER = 
VAR TopNumber = if(HASONEVALUE('TopN'[Top N]),VALUES('TopN'[Top N]),10)
    VAR Rest =
        COUNTROWS ( D_Client ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( 'Client Name'[Client Name]),
            IF (
                VALUES ( 'Client Name'[Client Name]) = "Others",
                SUMX (
                    TOPN (
                        Rest,
                        ADDCOLUMNS (
                            VALUES ( D_Client[Client Name]),
                            "Measure", [% Cont Client 2020]
                        ),
                        [Measure], ASC
                    ),
                    [Measure]
                )
            )
        )

The "Top X" works perfectly. However, The "other" category cannot show the correct number. 

If I chose "Top 4" , The other category was 100%.

If I chose "Top 8", The category was 40% ( It deducted "top 2" only ) 

How could I correct "the other" category? 

Any help is highly appreciated. 

[tracytran91]

Hi friends, 

I have made a dynamic Top N and other Category.

It works well when I chose Top 7. However, If I chose below top 7 such as top 3 , top 6 .... "the others" Category is 100%.

OTHER = 
VAR TopNumber = if(HASONEVALUE('TopN'[Top N]),VALUES('TopN'[Top N]),20)
    VAR Rest =
        COUNTROWS ( D_Client ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ( 'Client Name'[Client Name]),
            IF (
                VALUES ( 'Client Name'[Client Name]) = "Others",
                SUMX (
                    TOPN (
                        Rest,
                        ADDCOLUMNS (
                            VALUES ( D_Client[Client Name]),
                            "Measure", [% Cont Client 2020]
                        ),
                        [Measure], ASC
                    ),
                    [Measure]
                )
            )
        )

Top X = 
	VAR TopNumber =if(HASONEVALUE('TopN'[Top N]),VALUES('TopN'[Top N]),20)
    VAR Rest =
        COUNTROWS ( D_Client ) - TopNumber
    RETURN
        IF (
            HASONEVALUE ('Client Name'[Client Name]),
            CALCULATE (
               [% Cont Client 2020],
                FILTER (
                    D_Client,
                    [Client Name] = VALUES ( 'Client Name'[Client Name] )
                        && CONTAINS (
                            TOPN (
                                TopNumber,
                                ADDCOLUMNS (
                                    ALL ( 'Client Name'[Client Name]),
                                    "Contribution", CALCULATE (
                                        [% Cont Client 2020],
                                        FILTER ( D_Client, D_Client[Client Name] = EARLIER ( [Client Name] ) )
                                    )
                                ),
                                [Contribution], DESC
                            ),
                            'Client Name'[Client Name], VALUES ('Client Name'[Client Name] )
                        )
                )
            )
        )

Any help is high appreciated!

[v-easonf-msft]

Hi, @tracytran91 
Can you share detailed sample data for testing?
It is difficult for me to get more useful information just from the code.

Best Regards,
Community Support Team _ Eason