KPI - calculate median and max value

[adias102]

Hi,

I have a dashboard which displays several KPIs and I need to calculate 2 other KPIs: one for the median value and one for the max value.

I have 2 dimensions: Year and Stations.

I have 2 KPIs:

one shows the number of events for the chosen years and stations.
count(distinct EventID)

The second shows the toal number of events for the chosen years, ignoring the stations dimension.
count({$<Stations=>} distinct EventID)

How do I calculate the median and the max value for the stations? These KPIs should display a fix value, no matter which station is chosen (like the total number of events).

I tried something like median(aggr(count(distinct EventID), Stations)) but it only shows the correct value when no station is chosen. What is the correct syntax for the medin?

[Dalton_Ruer · Accepted solution]

You would need to introduce Set Analysis and use {1} which will exclude all user selections. 

I have attached a Set Analysis workshop I wrote and pages 3 and 4 will explain that to you. And then you have workshop in case you want to read more about it. 

[adias102]

Thank you, this was very helpful 🙂