TOPN in PowerBI DAX not arranging values in proper order

1
votes

I have been running into some issues with the TOPN function in DAX in PowerBI.

Below is the original dataset:

regions  sales
--------------
a        1191
b         807
c        1774
d         376
e         899
f        1812
g        1648
h           6
i        1006
j        1780
k         243
l         777
m         747
n          61
o        1637
p         170
q        1319
r        1437
s         493
t        1181
u         118
v        1787
w        1396
x         102
y         104
z         656

So now, I want to get the Top 5 sales in descending order.

I used the following code:

Table = TOPN(5, SUMMARIZE(Sheet1, Sheet1[regions], Sheet1[sales]), Sheet1[sales], DESC)

The resulting table is as follows:

regions  sales
--------------
g        1648
j        1780
c        1774
v        1787
f        1812

Any idea why this is happening?

1
powerbidaxpowerpivotpowerquerytop-n
"enter image description here" links to images are not recommended. – Ṃųỻịgǻňạcểơửṩ
Please paste in your dataset as a Table, so that we can use your actual data to try and help. – jeffreyweir

1 Answers

4
votes

According to Microsoft documentation this is working as intended.

https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax

Remarks TOPN does not guarantee any sort order for the results.

What you can do is to create a RANKX to sort by.