Determine topN

[elizabethvieira]

Hi all, 

I want to determine the top 5 areas by number of publications. I would like to represent the result in a pie chart. I do not want to use the filter option available in the filter pane.

I have the table that follows, and the count of UT gives the number of publications (this identifies a publication).

https://drive.google.com/file/d/1RvVzMcNi3Tpm5iioeopaOf8hsXzKWm34/view?usp=drive_link 

My expected result is as follows:

five	area
194	Maternal Mortality
128	Group B Streptococcus
91	Bilirubin
90	Gestational Mellitus
64	Preterm Labor

At this moment, I am using the following formula, but I get all the areas and not the top 5.

SUMX(TOPN(5, SUMMARIZE('impact (2)', 'impact (2)'[area], "Total Articles", count('impact (2)'[UT])), [Total Articles], DESC), [Total Articles])

Thanks in advance,

Elizabeth Vieira

[bhanu_gautam · Accepted solution]

@elizabethvieira , Try using below DAX

Top5Areas =
TOPN(
5,
SUMMARIZE(
'impact (2)',
'impact (2)'[area],
"Total Articles", COUNT('impact (2)'[UT])
),
[Total Articles],
DESC
)

[bhanu_gautam · Accepted solution]

Let's try different approach

First create a measure 

   PublicationCount = COUNT(TableName[UT])

Then create a new table by going to modelling tab 

TopAreas =

   TOPN(

       5,

       SUMMARIZE(

           TableName,

           TableName[Area],

           "PublicationCount", [PublicationCount]

       ),

       [PublicationCount],

       DESC

   )

@elizabethvieira , Did you tried to make table or measure?

[Irwan · Accepted solution]

hello @elizabethvieira 

the TOPN from your link looks fine. I tried making an example and the result is good.

Left one is total sum and right one is sum of top 5 (exclude E and G as the lowest 2).

also, if you want to show sum of top 5 in pie chart, you can do easier in visual filter as there is no calculation.

before visual filter

after visual filter (put your areas in Legend and your value in Values, then your value again in By Value in visual filter option). in here, change from SUM to COUNT if you want to have count result.

Hope this will help.

Thank you.

[Anonymous · Accepted solution]

Hi @elizabethvieira 

Thanks to Irwan and bhanu_gautam  for the quick reply and solution. The link you provided to the pbix file has privacy I can't open it. Here is my test data:

(1) We can create measures.

Count = COUNT('impact (2)'[UT])

Index = 
var _table=SUMMARIZE(ALLSELECTED('impact (2)'),[area],"count",[Count])
RETURN RANKX(_table,[count],,DESC)

(2) We can create tables.

TopAreas = 
   TOPN(
       5,
       SUMMARIZE(
           'impact (2)',
           'impact (2)'[area],
           "PublicationCount", [Count]
       ),
       [PublicationCount],
       DESC
   )

TopAreas2 = 
var _table=SUMMARIZE('impact (2)',[area],"count",[Count],"index",[Index])
RETURN SELECTCOLUMNS(FILTER(_table,[index]<=5),[area],[count])

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. 

[elizabethvieira]

Many thanks to all. 

I fixed my issues. It is now working well. 

Best, 

Elizabeth

[bhanu_gautam · Accepted solution]

@elizabethvieira , Try using below DAX

Top5Areas =
TOPN(
5,
SUMMARIZE(
'impact (2)',
'impact (2)'[area],
"Total Articles", COUNT('impact (2)'[UT])
),
[Total Articles],
DESC
)

[elizabethvieira]

Many thanks, but when using the expression I get the following comment : 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

[bhanu_gautam · Accepted solution]

Let's try different approach

First create a measure 

   PublicationCount = COUNT(TableName[UT])

Then create a new table by going to modelling tab 

TopAreas =

   TOPN(

       5,

       SUMMARIZE(

           TableName,

           TableName[Area],

           "PublicationCount", [PublicationCount]

       ),

       [PublicationCount],

       DESC

   )

@elizabethvieira , Did you tried to make table or measure?

[elizabethvieira]

I tried a measure. 

Following the suggestion, I get a table with all the counts by area. It seems that the function topn is not working as I get all the areas and not the top five. 

[elizabethvieira]

I followed this page: 

https://learn.microsoft.com/en-us/dax/topn-function-dax

[Irwan · Accepted solution]

hello @elizabethvieira 

the TOPN from your link looks fine. I tried making an example and the result is good.

Left one is total sum and right one is sum of top 5 (exclude E and G as the lowest 2).

also, if you want to show sum of top 5 in pie chart, you can do easier in visual filter as there is no calculation.

before visual filter

after visual filter (put your areas in Legend and your value in Values, then your value again in By Value in visual filter option). in here, change from SUM to COUNT if you want to have count result.

Hope this will help.

Thank you.