top 10 and other in stack chart

[Roman_Zalesskii]

Hi everyone, i'm trying to find a solution to the next problem. I need to build a stock chart with top 10 models and other values divided by date. Here is what i need it to look like.

I've found different solutions but it isn't't exactly what I want. Usually "other" is in a separate column. But I need it in the same column with top 10 models.

Hope you can help me.

[v-yangliu-msft · Accepted solution]

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

[PaulDBrown]

Seeing as we are dealing with prices, how are you calculating the value for "other"? Average?

[Roman_Zalesskii]

No, it's a sum. Sum for each of the model from top 10 and sum for the rest of the models.

[PaulDBrown · Accepted solution]

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 

[Roman_Zalesskii]

Thank you a lot, but i can't figure out one last thing. 

What if I want to display top 10 models not for each month, but, for example, last year total sales?

[PaulDBrown]

Use Option B with a year slicer or applying a filter in the filter pane

[Roman_Zalesskii]

I did and still don't get what I needed. And for some reason some columns have more than 10 models.

[PaulDBrown]

Try using this measure instead of the [Sum Price] in the two measure:

2021 Prices = CALCULATE([Sum Price], FILTER(Date Table, Date Table[Year] = 2021)).

If you prefer to keep it dynamic (so in 2023 you will see 2022) use

Last year's Prices = CALCULATE([Sum Price], FILTER(Date Table, Date Table[Year] = YEAR(TODAY()) -1))

(no need for slicers or filters)

You are probably getting more than 10 models because there are models with the same rank. You probably also should change the expression DENSE for SKIP in the RANKX code. 
Say you have two models  ranked 3: DENSE will make the following model rank 4; SKIP will deliver 5 for the following model's rank. So it depends what you want to see.

[Roman_Zalesskii]

I figured out why i was getting more than 10 models. I had date hierarchy enabled, so top 10 applied only on the top level of the hierarchy which was years. When I disabled it for my file it started to display what I expected.

New measure working fine, but i can't figure out why I can't change it to months to get dynamic values for past 12 months.

As you can understand I'm pretty new to Dax.

[PaulDBrown]

For the last 12 months, do you want  whole calendar year (so from today 3 oct 2022 back to 4 oct 2021), or Nov 2021 to October (whole month) 2022?

[Roman_Zalesskii]

Whole month.

I've tried using DATESINPERIOD and  DATESBETWEEN, but the return either a blank chart, or some abstract lines that do make any sense.
Here an examples 

[Roman_Zalesskii]

Nevermind, I think I figured it out. I used this measure

Last year's Prices =

var maxindex = MAX('base table'[month])

var minindex = EOMONTH( maxindex,-12)

return

CALCULATE

 (SUM(Base table[Price]),

FILTER('date',

'date'[Date]<=maxindex &&

'date'[Date]>minindex))

And then last 12 month for the visual. The problem was with Edate, which returned values as seen in the screenshots above. 

I replaced it with EOMonth and it seems to work fine now.
Thank you anyway. 

[v-yangliu-msft · Accepted solution]

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

[Roman_Zalesskii]

Thank you for answer but unfortunately it's not what i'm looking for.

My bad, I should have included some data.

So I have 3 columns: 1) with date of order; 2) model name which customer bought; 3) and price for each model

And I want to divide all models by sales amount: top 10 models and the rest. like on the screenshot before. "Other" is not a separate column it's all in "models"