Forum Discussion
Helper Itop 10 and other in stack chart
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.
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
13 Replies
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_ZalesskiiThank 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"
PaulDBrownCommunity Champion
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
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