topic Re: Dynamic dashboard that shows TOP N and OTHER values in DAX Commands and Tips

Dynamic dashboard that shows TOP N and OTHER values

Anonymous — Mon, 08 Jun 2020 09:51:10 GMT

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.

Re: Dynamic dashboard that shows TOP N and OTHER values

stevedep — Mon, 08 Jun 2020 11:14:55 GMT

This article might be what you are looking for.

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

Re: Dynamic dashboard that shows TOP N and OTHER values

Anonymous — Mon, 08 Jun 2020 12:11:28 GMT

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

Re: Dynamic dashboard that shows TOP N and OTHER values

stevedep — Tue, 09 Jun 2020 08:22:04 GMT

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.

Re: Dynamic dashboard that shows TOP N and OTHER values

stevedep — Fri, 12 Jun 2020 07:18:49 GMT

Were you able to solve this one?