Forum Discussion
Top 3 N and Others With Filters
So I have been spending some time searching across the forums and I don't think I have found the solution I am looking for.
Problem: Basically I have a table containing products that returns a profit value and I have created a new table that takes the same product names and added Others, where I created a measure that uses the original table to return a list of the top 3 products' profit value and put all the other product's combined value into others, which I can display in a piechart using the new table's product names and Others as the legend.
The issue is when I filter on Others, the charts that are connected with it ie: a table that shows all the products and their profit value individually, will not correctly show which products belong in the Others section, rather it will turn the chart blank because the table does not comprehend what products were read as others.
This is the Top 3 and Others calculation measure
Top3ProfitandOthersRanking =
VAR TopCatTable =
TOPN(
3,
ALLSELECTED(OthersProductsRanking), //Table containing names of products and others
[AggregateProfit] //Measure used to calculate profit
)
VAR TopCat =
CALCULATE(
[AggregateProfit],
KEEPFILTERS(TopCatTable)
)
VAR OtherCat =
CALCULATE(
[AggregateProfit],
ALLSELECTED(OthersProductsRanking)
) -
CALCULATE(
[AggregateProfit],
TopCatTable
)
VAR CurrentCat = SELECTEDVALUE(OthersProductsRanking[Products])
RETURN
IF(
CurrentCat <> "Others",
TopCat,
OtherCat
)
This helps me display the correct values of the top 3 products and others where I use the Top3ProfitAndOthersRanking as my value and using OthersProductsRanking[Products] as my legend because it contains the top 3 products and Others as names for the legend.
The issue with this is that my original table might have provided the values using the [AggregateProfit] measure, however the original table would not be able to identify what exactly is Others in this context, even when I connect the tables together between the original table and OthersProductsRanking using [Products].
Therefore, any charts associated with the original table will show as blank when I select Others in the Piechart I have created.
3 Replies
I don't think you are understanding what I am trying to achieve here. With the code I have, I have already achieved the "Top 3 and Others" effect. This article does not answer my question.
What I am asking is whether or not there is a way to be able to click on others and be able to see what products are within that category? Creating Others artificially prevents me from seeing the breakdown because it does not exist in the original table.- lbendlin
Super User
Create a measure that identifies if a product is grouped as "other" in the current filter context. Use that measure as a visual filter for a table visual with the product names. Most likely that will require a disconnected table.
