top 10 and other in stack chart

Hi  Roman_Zalesskii ,

I created some data:

Here are the steps you can follow：

1. Copy two of these tables as Table2 and Table3 in Power BI.

Click [Other] - Right click - Remove column

Table3：

Click on [Groupr] and [Amount] - Right click - Remove column

Click on  [Other] – Unpivot Columns.

Select New Columns - Change Names to [Group], [Amount]

2. Append Queries to both tables.

Result：

3. Result:

Best Regards,

Liu Yang

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

Ok, here is one way. Set up the model with a Date Table, a dimension table for Model, and a discnnected table (I've called it Disconnected Legend). For this Disconnected Legend Table, I've used:

Disconnected Legend =
VAR _Model =
    ADDCOLUMNS (
        VALUES ( fTable[Model] ),
        "Order", RANKX ( VALUES ( fTable[Model] ), fTable[Model],, ASC, DENSE )
    )
VAR _Other = { ( "Other", 1000000000000 ) }
RETURN
    UNION ( _Model, _Other )

(Use the Order column to sort the model column)

The model looks like this:

Next create the measures (in my example I'm showing top 3 & Other, so adapt the measures to your needs for Top10 & Other):

Option A) TopN by date & other:

SUM Price = 
SUM('fTable'[Price])

RANK by Date =
IF (
    ISBLANK ( [SUM Price] ),
    BLANK (),
    RANKX ( ALL ( 'Model Table'[Model] ), [SUM Price],, DESC, DENSE )
)

Value for visual =
VAR _T3 =
    CALCULATE (
        [SUM Price],
        FILTER ( 'Model Table', [RANK by Date] <= 3 ),
        TREATAS ( VALUES ( 'Disconnected Legend'[Model] ), 'Model Table'[Model] )
    )
VAR _Other =
    CALCULATE (
        [SUM Price],
        FILTER ( ALL ( 'Model Table'[Model] ), [RANK by Date] > 3 )
    )
RETURN
    IF ( MAX ( 'Disconnected Legend'[Model] ) = "Other", _Other, _T3 )

Create the Stacked column visual with the Date Table[Date] for the x-axis, the Disconnected Legend[Model] for the Legend, and the [Value for visual] for the y-axis to get:

Option B: Overall TopN & Others:

RANK by Model =
IF (
    ISBLANK ( [SUM Price] ),
    BLANK (),
    CALCULATE (
        RANKX ( ALL ( 'Model Table'[Model] ), [SUM Price],, DESC, DENSE ),
        ALL ( 'Date Table' )
    )
)

Value for visual All dates =
VAR _T3 =
    CALCULATE (
        [SUM Price],
        FILTER ( 'Model Table', [RANK by Model] <= 3 ),
        TREATAS ( VALUES ( 'Disconnected Legend'[Model] ), 'Model Table'[Model] )
    )
VAR _Other =
    CALCULATE (
        [SUM Price],
        FILTER ( ALL ( 'Model Table'[Model] ), [RANK by Model] > 3 )
    )
RETURN
    IF ( MAX ( 'Disconnected Legend'[Model] ) = "Other", _Other, _T3 )

Sample PBIX attached