Show top 5 KPIs

[DAXNewbie2023]

Hi All - I am building a consolidated dashboard that pools data from Adobe & Google Analytics. My ultimate goal is to compare the KPIs (around 30 in number) for the same month between Current & Previous year and show the percentage change (increase/decrease) with an option to chose a month from a slicer. I am able to achieve this by creating a measure using the DAX formula 'Calculate' *(Bounce Rate Percentage Change = (CALCULATE(SUM('AEM'[Bounce Rate_AEM]),'AEM'[Year] = 2023)-CALCULATE(SUM('AEM'[Bounce Rate_AEM]),'AEM'[Year] = 2022))/CALCULATE(SUM('AEM'[Bounce Rate_AEM]),'AEM'[Year] = 2022))* 

I have created 30 measures (1 for each KPI) and housed all these under one table.

Now, the problem is that the end user wants to select a Month and wants to see the list of top 5 and bottom 5 performing KPIs for that particular month.

For example - if the Month of January has 30 KPIs, let's say - KPI1, KPI2, KPI3,....KPI30 and if KPI3, KPI5, KPI7, KPI12, KPI15 have the highest changes for January, then the user wants to see these 5 only in a table or some visualization (If possible, along with the percentage change for each KPI). Same for bottom 5 performing KPIs as well.

Can someone give me an idea on how this can be achieved using DAX or some visualization with filters applied?

Year	Month	Page views	Download	Video Played	Visits	Visitors	Page views % Change	Download % Change	Video Played % Change	Visits % Change	Visitors % Change
2022	Jan	7379	4874	8741	2853	8816
2022	Feb	8581	4049	5044	4689	2100
2022	Mar	8650	1514	6949	5133	2857
2022	Apr	1207	1239	4506	1713	7181
2022	May	5136	9269	5022	1541	6744
2022	Jun	2161	8292	6343	6684	1642
2023	Jan	6592	9319	4809	6665	3451	-11%	91%	-45%	134%	-61%
2023	Feb	8022	8063	5457	1133	2047	-7%	99%	8%	-76%	-3%
2023	Mar	6495	4908	2837	8794	5426	-25%	224%	-59%	71%	90%
2023	Apr	3394	1728	2891	2927	9274	181%	39%	-36%	71%	29%
2023	May	2799	4703	6878	9398	4840	-46%	-49%	37%	510%	-28%
2023	Jun	2673	3831	9879	4813	2515	24%	-54%	56%	-28%	53%

[v-tangjie-msft]

Hi @DAXNewbie2023 ,

Unfortunately, The current system does not support this function. 

If you would like to suggest  feature improvements, you may  vote the idea and comment here to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[DAXNewbie2023]

Thanks for the response @v-tangjie-msft .

Can you please possibly think and let me know if there's any other way approaching this problem? I am open to trying any different approach that could potentially achieve the end goal of the user. Thanks in advance!

[v-tangjie-msft]

Hi @DAXNewbie2023 ,

I'm sorry to say that through my testing there is no replacement program. You can try to create a measure with all the values and filter it by topn function.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.