topic Determine topN in DAX Commands and Tips

Determine topN

elizabethvieira — Wed, 04 Sep 2024 10:57:07 GMT

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

Re: Determine topN

bhanu_gautam — Wed, 04 Sep 2024 11:13:03 GMT

elizabethvieira , Try using below DAX

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

Re: Determine topN

elizabethvieira — Wed, 04 Sep 2024 11:23:03 GMT

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"

Re: Determine topN

bhanu_gautam — Wed, 04 Sep 2024 11:27:13 GMT

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(

SUMMARIZE(

TableName,

TableName[Area],

"PublicationCount", [PublicationCount]

[PublicationCount],

DESC

)

elizabethvieira , Did you tried to make table or measure?

Re: Determine topN

elizabethvieira — Wed, 04 Sep 2024 11:36:46 GMT

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.

Re: Determine topN

elizabethvieira — Wed, 04 Sep 2024 11:40:05 GMT

I followed this page:

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

Re: Determine topN

Irwan — Thu, 05 Sep 2024 03:08:50 GMT

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.

Re: Determine topN

Anonymous — Thu, 05 Sep 2024 02:58:36 GMT

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.

Re: Determine topN

elizabethvieira — Fri, 06 Sep 2024 08:00:02 GMT

Many thanks to all.

I fixed my issues. It is now working well.

Best,

Elizabeth