ASC in TOPN not working

DAX DAX Calculations
1

Hi,
My DAX code with TOPN DESC works fine although the column is not sorted as need. But the bigger issue I have is I cannot get the ASC (bottom) to work properly. It returns all the rows and ignores the TOPN filter, e.g, top 5 (ASC) are not returned rather the whole rows in the column come through.
My DAX code:

Top 5 Customers Per Region = 
VAR
 RankingContext = VALUES( 'Customers'[Customer Name] )
RETURN
CALCULATE( [Sales Amount],
   TOPN( 5,
	ALL( 'Customers'[Customer Name] ),
	[Sales Amount] ),
  RankingContext)

The below does not work:

Bottom 5 = CALCULATE( [Sales Amount],
TOPN ( 5, VALUES ( Customers[Customer Name] ), [Sales Amount], ASC ))

I equally tried:

Bottom 5 Customers = 
VAR CustomerRankX = 
 RANKX ( ALL (Customers[Customer Name] ), 
 SUMX 
  ( Orders, Orders[Sales Quantity] * Orders[Unity Price] ),,ASC )
RETURN 
 IF (CustomerRankX <= 5,
  SUMX ( Orders, Orders[Sales Quantity] * Orders[Unity Price] ) , BLANK() )

and the below did not work either:

Bottom 3 Customers Per Region = 
VAR
RankingContext = VALUES( Customers )
RETURN
CALCULATE( [Sales Amount],
TOPN( 3,
	ALL( Customers[Customer Area], Customers[Customer Name] ),
	[Sales Amount],ASC, [Sales Amount] ),
RankingContext )

I want to create a report that shows the top 5 and the bottom 5 customers per region against the value of sales.
I anticipate your help.
Thank you

2

Hi @upwardD
Unfortunately, the TOPN function only filters the TOP customers but the result you obtain among the TOPs is not sorted.

You can obtain a list of the sorted TOP customers per region using the following measure:

Top 5 Customer Names per region =
VAR TopCust = TOPN (
    5,
    VALUES
        ( Customers[Customer Name] ),
       [Sales Amount],
        ASC
) 
RETURN CONCATENATEX ( TopCust, Customers[Customer Name], " , ",[Sales Amount], ASC )

![Capture|690x222](upload://1I1NycPek0cFsi4KUbWXD1oJoY.png) 

I hope it helps you,

Diego
2 Likes
3

Hi @upwardD,

Try using these measures

Bottom 5 per  Region = 

IF (
    ISINSCOPE ( Customers[Customer Name] ),
    RANKX (
        CALCULATETABLE (
            VALUES ( 'Customers'[Customer Name]),
            ALLSELECTED ( Customers[Customer Name] )
        ),
        [Sales Amount],,ASC
    ),
    IF (
        ISINSCOPE ( Customers[Customer Area] ),
        VAR AreaSales = [Sales Amount]
        RETURN
            CALCULATE (
                RANKX (
                    VALUES ( 'Customers'[Customer Area] ),
                    [Sales Amount],
                    AreaSales,ASC
                ),
                ALLSELECTED ()
            )
    )
) 



Top 5 Customers Per Region = 

IF (
    ISINSCOPE ( Customers[Customer Name] ),
    RANKX (
        CALCULATETABLE (
            VALUES ( 'Customers'[Customer Name]),
            ALLSELECTED ( Customers[Customer Name] )
        ),
        [Sales Amount]
    ),
    IF (
        ISINSCOPE ( Customers[Customer Area] ),
        VAR AreaSales = [Sales Amount]
        RETURN
            CALCULATE (
                RANKX (
                    VALUES ( 'Customers'[Customer Area] ),
                    [Sales Amount],
                    AreaSales
                ),
                ALLSELECTED ()
            )
    )
)

Then apply Visual Filter

1
11160×568 80.4 KB

Top5 and Bottom 5 _EDNA.pbix (493.0 KB)

3 Likes
4

It works! Thank you so very much

5

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Group. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!