DAX Workout 025 - Top and Bottom 5

Workouts DAX
1

Challenge: Top and Bottom 5

In this workout, you will create one measure that will give you the Top 5 and the Bottom 5 of Total Sales by selecting which one you want to see using a slicer.

You will need to create a table for your options and a measure for the SELECTEDVALUE . Then one measure for TOP 5 and BOTTOM 5.

Best of luck to you!

Screenshot 2023-06-14 153135
Screenshot 2023-06-14 1531351437×807 154 KB

Submission

Load the supplied data file into a new Power BI file, create your solution, and reply to this post. Upload a screenshot of your solution along with the DAX measure. Please format your DAX code and blur it or place it in a hidden section.

Period
This workout will be released on Monday, June 19, 2023, and will end on Saturday, June 24, 2023. But you can always come back to any of the workouts and solve them.

DAX _25 - Top5Bottom5 06182023.pbix (686.0 KB)

2

Workout 25 TOP5BOTTOM5
Workout 25 TOP5BOTTOM51252×778 88.5 KB

DAX _25 - Top5Bottom5 06182023.pbix (688.8 KB)

2 Likes
3

Hi Everyone.
thanks again @Paul.Gerber for this workout.
this screenshot is my submission.

sss
sss1433×787 66.8 KB

1 Like
4

image
image2755×1275 243 KB

1 Like
5

image

DAX Code 
Top/Bottom 5 Sales =
VAR _CustomerSales_ =
    CALCULATETABLE (
        SUMMARIZE ( Sales, Customers[Customer Names], "@Sales", [Total Sales] ),
        ALLSELECTED ( Customers[Customer Names] )
    )
VAR _Top5_ =
    FILTER (
        TOPN ( 5, _CustomerSales_, [@Sales], DESC ),
        Customers[Customer Names] IN VALUES ( Customers[Customer Names] )
    )
VAR _Bottom5_ =
    FILTER (
        TOPN ( 5, _CustomerSales_, [@Sales], ASC ),
        Customers[Customer Names] IN VALUES ( Customers[Customer Names] )
    )
VAR _Result =
    SWITCH (
        SELECTEDVALUE ( Slicer[Value] ),
        "Top 5",    SUMX ( _Top5_,    [@Sales] ),
        "Bottom 5", SUMX ( _Bottom5_, [@Sales] )
    )
RETURN
    _Result
2 Likes
6

image
image

Summary 
Top 5 Rank Sales =
VAR __holdRank =
    RANKX ( ALLSELECTED ( Customers ), [Total Sales],, DESC, DENSE )
RETURN
    IF (
        HASONEVALUE ( Customers[Customer Names] ),
        IF ( __holdRank <= 5, [Total Sales], BLANK () ),
        SUMX (
            FILTER (
                ADDCOLUMNS (
                    ALLSELECTED ( Customers[Customer Names] ),
                    "Total Sales", [Total Sales],
                    "Rank", RANKX ( ALLSELECTED ( Customers[Customer Names] ), [Total Sales],, DESC, DENSE )
                ),
                [Rank] <= 5
            ),
            [Total Sales]
        )
    )

Bottom 5 Rank Sales =
VAR __holdRank =
    IF (
        ISBLANK ( [Total Sales] ),
        BLANK (),
        RANKX (
            ALL ( Customers ),
            CALCULATE ( SUM ( Sales[Line Total] ) ),
            ,
            ASC,
            DENSE
        )
    )
RETURN
    IF (
        HASONEVALUE ( Customers[Customer Names] ),
        IF ( __holdRank >= 2 && __holdRank <= 6, [Total Sales], BLANK () ),
        SUMX (
            FILTER (
                ADDCOLUMNS (
                    ALLSELECTED ( Customers[Customer Names] ),
                    "Total Sales", [Total Sales],
                    "Rank", RANKX ( ALLSELECTED ( Customers[Customer Names] ), [Total Sales],, ASC, DENSE )
                ),
                [Rank] <= 6
            ),
            [Total Sales]
        )
    )
1 Like
7

DAX _25 - Top5Bottom5 06182023.pbix (710.6 KB)

Solved in 2 different ways

DAX Codes:

1st way: Making use of Top 5 /Bottom 5 as radio button

Range Table:{“Ranges” , “List” }, { “Top 5”, “Bottom 5”}
Rankings =
VAR _top = RANKX( ALL( Customers[Customer Names] ), [Total Sales], ,DESC)
Var _bottom = RANKX( ALL( Customers[Customer Names] ), [Total Sales] , ,ASC )
Var _result = IF( SELECTEDVALUE( Range[Ranges] ) = “Top 5”, _top, _bottom )

RETURN
    IF( _result <= 5 , [Total Sales] )

2nd Way
Top 5 =
CALCULATE( [Total Sales] ,
FILTER( VALUES(Customers[Customer Names] ),
IF( RANKX( ALL( Customers[Customer Names] ), [Total Sales] ) <= 5,
[Total Sales] , BLANK() ) ) )

Bottom 5 =
CALCULATE( [Total Sales],
FILTER( VALUES(Customers[Customer Names] ),
IF( RANKX( ALL( Customers[Customer Names] ), [Total Sales], ,ASC ) <= 5,
[Total Sales] , BLANK() ) ) )

8

Answer

image
image863×433 31.7 KB

image
image832×428 33.4 KB

Selected 5 =
SELECTEDVALUE(‘Table’[Top/Bottom])

TOP_BOTTOM Sales =

VAR RankTop = RANKX(ALL(Customers[Customer Names]),[Total Sales],DESC)

var RankBottom = RANKX(ALL(Customers[Customer Names]),[Total Sales],ASC)

var TopSales = if(RankTop <=5, [Total Sales], BLANK())

var BottomSales = IF(RankBottom >1 && RankBottom <= 106,[Total Sales],BLANK())

VAR Solution =

SWITCH(TRUE(),

[Selected 5] = “Top_5”, TopSales,

[Selected 5] = “Bottom_5”, BottomSales,

BLANK())

return

Solution

DAX _25 - Top5Bottom5 Solution.pbix (689.7 KB)

9

answer:

image
image1321×744 69.6 KB

DAX Workout No25 MB.pbix (726.0 KB)