Find top 10 by sales, and keep rest of the items under "Others" category.

[Anonymous]

Hello all,

I am working on a data that has many tables. 
I want to show the top 10 products ( product column exists in table-1) by their sales ( in Table-3) and put rest of all the product and their sales under "Others" category (taking sum of sales of all remaining products). 

The sales column contains many rows for each product. So I want to add them to represent with a single value (summed up) for each product.

Both the columns exist in two different tables, that are connected with an another intermediate table (table-2).

* Table-1 (having products) has One-to-many relationship with Table-2.
* Table-2 has One-to-one relationship with table-3.

https://1drv.ms/u/s!Aln5pmqPCnTphKNPwl5AirUGEJ5aaQ?e=g2AS2W 

Please help in finding the right solution.
Suggestion will surely be appreciated and considered.

Regards
Varun

[amitchandak]

@Anonymous , refer to this video from Curbal  , if this can help https://www.youtube.com/watch?v=UAnylK9bm1I

[Anonymous]

Hello @amitchandak<,
Thank you for your response.

I have watched the video, but the major issues is that I have my coulms in two different tables. Otherwise, I have tried RankX myself, but could not resolvse the isssue.

I have also shared the link for the sample data on the post to refer with the case, that I have been stuck in! 

Is there any DAX that you can write and help?

[v-yingjl · Accepted solution]

Hi  @Anonymous  ，

You can try to create a total measure and a column like the video mentioned:

Total Sales = 
CALCULATE(
    SUM('Table-3'[Sales]),
    ALLEXCEPT(
        'Table-3',
        'Table-3'[Location]
    )
)

Sales TopN+Others = 
var _rank = RANKX(ALL('Table-3'),[Total Sales],,DESC)
return
IF(
    _rank <= 10,[Location],"Others"
)

Or you can try this way:

Create a new table like this:

Create these measures:

Total Sales = 
CALCULATE(
    SUM('Table-3'[Sales]),
    ALLEXCEPT(
        'Table-3',
        'Table-3'[Location]
    )
)

Sales Amount Other = 
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        EXCEPT(
            ALL('Table-3'[Location]),
            TOPN(
                10,ALL('Table-3'[Location]),[Total Sales]
            )
        )
    )
)

Sales Amount Other Total Only = 
IF(
    NOT(
        ISFILTERED('Table-3'[Location])
    ),
    [Sales Amount Other]
)

Sales Amount Top = 
CALCULATE(
    [Total Sales],
    KEEPFILTERS(
        TOPN(
            10,ALL('Table-3'[Location]),[Total Sales]
        )
    )
)

Sales Amount Top & Others = 
IF(
    HASONEVALUE('Top & Other'[Top]),
    SWITCH(
        VALUES('Top & Other'[Top]),
        "Top10",[Sales Amount Top],
        "Other",[Sales Amount Other Total Only]
    ),
    [Total Sales]
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.