RankX - Trying to create table that displays dynamic top 10 with an other group

[nalanis]

Hello,

I am working on a table in Power BI and am trying to display a dynamic top 10 with "Other" based on Current Year Sales (CY Sales). However, I am only able to get the top 10 to display but not the Others. I have scoured the web looking for a solution and came across the following measure: 

Top10 = IF(Sales[Rank (Coord_Company)] <= 10, Sales[Sum of CY Sales], IF(HASONEVALUE(Sales[Coord Company]),IF(VALUES(Sales[Coord Company]) = "Others", SUMX( FILTER(ALL(Sales[Coord Company]),Sales[Rank (Coord_Company)] > 10), Sales[Sum of CY Sales]))))

However, when I apply the formula it does not provide the "Other" group. What am I missing? Coord Company is a column, Top 10 is a measure, and Rank(Coord_Company) is a measure. 

Any feedback is appreciated. Thank you

[v-jiascu-msft]

Hi @nalanis,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @nalanis,

Did you apply any filters in this visual. It should show up all the companies. 

You missed a parameter of your formula.

Top10 =
IF (
    Sales[Rank (Coord_Company)] <= 10,
    Sales[Sum of CY Sales],
    IF (
        HASONEVALUE ( Sales[Coord Company] ),
        IF (
            VALUES ( Sales[Coord Company] ) = "Others",
            SUMX (
                FILTER ( ALL ( Sales[Coord Company] ), Sales[Rank (Coord_Company)] > 10 ),
                Sales[Sum of CY Sales]
            ),"missed"
        )
    )
)

Do you want show up the top 9 companies and "Others" and ignore the rank of "Others"?

Try this formula and filter the Top10 in the visual level filter with "is not blank".

Top10 =
IF (
    Sales[Rank (Coord_Company)] <= 10,
    Sales[Sum of CY Sales],
    IF (
        HASONEVALUE ( Sales[Coord Company] ),
        IF (
            VALUES ( Sales[Coord Company] ) = "Others",
            SUMX (
                FILTER ( ALL ( Sales[Coord Company] ), Sales[Rank (Coord_Company)] > 10 ),
                Sales[Sum of CY Sales]
            ),
           blank()
        )
    )
)

Best Regards!

Dale

[nalanis]

Hi Dale,

I would like to display top 10 and then everything else wrapped up in "Other" without a ranking. I applied your second formula but am still missing the "Other" group. I think I know the problem though. In my coordinating company column data I do not have an "Other" listed so it isn't returning anything. So what I need to do is convert the Coord Company to a new column and create an entry of "Other". However, how would I go about doing that?

I created a rank measure with the following syntax: Rank (Agency) = IF(HASONEVALUE(Sales[Agency]),RANKX(ALL(Sales[Agency]),CALCULATE([CY Sales])))

Is there a way I can apply this measure to create an "Other" group?

Below are my results from applying the second formula.

Here are my filters as well.

Thank you

[v-jiascu-msft · Accepted solution]

Hi @nalanis,

You are right. No "Others" exists. That's the cause. 

1. Create a new table for all the Companies.

Companies =
UNION (
    FILTER (
        DISTINCT ( VALUES ( Sales[Coord Company] ) ),
        ISBLANK ( [Coord Company] ) = FALSE ()
    ),
    { "Others" }
)

2. Create a relationship between "Sales" and "Companies".

3. Create a new measure of Ranks.

RankxCompany =
RANKX (
    ALL ( 'Companies'[Coord Company] ),
    CALCULATE ( SUM ( Sales[CY Sales] ) )
)

4. Create a measure "Top10".

Top10 =
VAR ComRank = [RankxCompany]
RETURN
    IF (
        ComRank < 10,
        SUM ( Sales[CY Sales] ),
        IF (
            MIN ( 'Companies'[Coord Company] ) = "Others",
            CALCULATE (
                SUMX (
                    SUMMARIZE (
                        'Companies',
                        Companies[Coord Company],
                        "CQuantity", SUM ( Sales[CY Sales] ),
                        "CRank", RANKX (
                            ALL ( Companies[Coord Company] ),
                            CALCULATE ( SUM ( Sales[CY Sales] ) )
                        )
                    ),
                    IF ( [CRank] >= 10, [CQuantity], 0 )
                ),
                ALL ( Companies )
            ),
            BLANK ()
        )
    )

5. Create a visual, filter "Top10" with "is not blank".

Notes: 

1. Only top 9 and the total of all the left will be displayed when you apply the filter.

2. As you can see, the result of "Others" is correct.

3. I tried to modify the formula to fit your scenario. Please make some changes yourself.

Best Regards!

Dale