Dynamic dashboard that shows TOP N and OTHER values

[Anonymous]

Hello everyone,

my objective is to create a measure that show best N values and the sum of others, in a context of dashboard where, as the user click a measure, the graphics change the content. 

Moreover, the dataset is of huge dimension, that makes it harder and also the performance should be considered.

Here is a semplification and explanation:

SALES
ProductID	Sales	Quantity	Profit	Discount
A1	210	3	30	0
A1	70	1	10	0
A2	180	2	40	0,2
B1	50	2	7	0,4
B2	70	7	9	0
B2	140	14	18	0

The fact table has many dimensions that are related to the final dashboard; to make the dashboard dynamic, I create a SWITCH measure that refers to an unrelated table, that looks like this:

DIMENSION

TotalSales

TotalQuanity

TotalProfit

TotalDiscount

its aim is to calculate each dimension, based on the user selection.

I tried many different approaches but really can't get how to make it working as I want;

Hope that I've been exhaustive,

Thanks to everyone that answers,

Dario.

[stevedep · Accepted solution]

This article might be what you are looking for.

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

[Anonymous]

Thanks, it is good, but I m looking for something better in terms of performance

[stevedep]

Best to work with calculated columns then. Do a topn and add a new column with an IF statement of the product name or label it as other. This is not dynamic / will be calculated at load time, but will perform way better.

[stevedep]

Were you able to solve this one?