cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Conzo12
New Member

Can I use the TopN Function in DAX to order data by another column

Hi all, I am brand new to this forum so I apologise in advance if I do not give all the information that is required. I will however try and explain this as best as I can. I have a table as follows:

 

I have a table with thousands of rows. The two key columns of interest are included in the sample table I have provided below.

Order NumberCountry
36478849Ireland
36478849Ireland
22761993USA
48475793Germany
48475793Germany
48475793Germany
12436657USA

 

I am trying to create a visual that gives me the Top country by number of sales order numbers. I also have some other filters that I have to apply but this isn't relevant to my query so I have not included the fields above. My desired result is for it to say:

 

Germany3

 

I did find away to achieve this using the filter menu as it allows you to do a Top N on the "Country" field but actually use the "Order Numbers" as the value it looks at. Please see below (Legal Entity is Country in my example). 

 

Conzo12_0-1643070354826.png

The reason why this isn't good enough for me is because I need to be able to further manipulate this data into a pertage of other figures. This is why my thoughts were to create a meausre and to use the TopN function as part of a Calculate. Please see my DAX formula below:

 

GreatestLEnotinCETotal = CALCULATE(COUNT(TABLE1[SALESORDERNUMBER]),TABLE1[IsValueInCE]="No",TOPN(1,TABLE1,FO_OrderLines[LEGALENTITYNAME],0))
I initially had thought this had worked as it give me the exact total I needed (i.e Germany     3) but then I noticed that instead (unlike in the filter menu above) it takes the Top one alphabetically or ascending or decending and doesn't allow for sorting on another column (in my case sales order number by country with the most occurances). Please let me know if you have any information that can help me or require any more info from my side.
 
Thank you in advance.
 
Conzo 
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much Ashish. That worked great.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
StefanoGrimaldi
Super User
Super User

it depend on what you enter to the dax topn parameters, its giving you the alphabetically order cause you use in the parameter the legaenititname instead of a value, if oyu want the topn by value, need to enter the column reference to the value result not the name 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors