topic Top 3 per category in a pivot table in DAX Commands and Tips

Top 3 per category in a pivot table

Anonymous — Sun, 28 Aug 2022 06:13:49 GMT

Hello!

I am trying to group the top 3 stores for a metric by their district in the same pivot table. Example below...

I would like to show the top 3 in each district. Any help is appreciated!!!

Re: Top 3 per category in a pivot table

daXtreme — Sun, 28 Aug 2022 07:03:01 GMT

Create a helper measure that will return 1 for any top 3 stores in any of the districts and then filter the visual by this value on this very measure. This will then hide the other stores and you'll only see what you want.

Re: Top 3 per category in a pivot table

Jihwan_Kim — Sun, 28 Aug 2022 07:42:35 GMT

Hi,

In case you want to create a table, please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Top three per district table = VAR _topthreestorelist = SUMMARIZE ( GENERATE ( VALUES ( Data[District] ), SUMMARIZE ( TOPN ( 3, FILTER ( Data, Data[District] = EARLIER ( Data[District] ) ), Data[Sales], DESC ), Data[Store] ) ), Data[Store] ) RETURN CALCULATETABLE ( Data, Data[Store] IN _topthreestorelist )

Re: Top 3 per category in a pivot table

CNENFRNL — Sun, 28 Aug 2022 08:29:59 GMT

TOP_N = VAR __topn = TOPN( MAX( N[Value] ), CALCULATETABLE( SUMMARIZE( Data, Data[District], Data[Store] ), ALLSELECTED( Data[Store] ) ), [Total] ) RETURN CALCULATE( [Total], KEEPFILTERS( __topn ) )