DAX Workout 006 - Top N Sales and the Bottom N Costs

[donnclark]

Here’s my (late) submission on DAX Workout #6:
DAX _6 Highlight Top Bottom N DEC.pbix (557.2 KB)

[MubarakBabs]

The easiest workout I’ve faced so far. I don’t know if that’s because I’m getting more comfortable with DAX or the workout was just straightforward. I guess I’ll see when I take on the next workouts

I used the results of the DAX as the background colour in conditional formatting

Highlight N = GENERATESERIES(1, 10, 1)

Top N sales = 
 VAR salesrank = RANKX(ALLSELECTED(Products), [Total Sales], , DESC)
 RETURN
 IF(salesrank<= 'Highlight N'[Highlight N Value], "#E69571")

Bottom N Costs = 
 VAR costrank = RANKX(ALLSELECTED(Products), [Total Costs], , ASC)
 RETURN
 IF(costrank <= 'Highlight N'[Highlight N Value], "#46A7E6")

[Paul.Gerber]

Great job, @MubarakBabs it could be a little of both. Keep going!

[shahadathossen.shajj]

Here is my submission

Bottom N Costs =
VAR _highlightedNumber = SELECTEDVALUE( 'Table'[Number of Highlighted Products] ) 
VAR _table = SELECTCOLUMNS(
    TOPN(
        [Highlighted Number],
        ALL( Products ),
        [Total Sales],
        ASC
    ),
    "Name", Products[Product Name]
) 

RETURN
IF(
    SELECTEDVALUE( Products[Product Name] ) IN _table,
    "Green", BLANK()
)

Top N Sales =
VAR _highlightNumber = SELECTEDVALUE( 'Table'[Number of Highlighted Products] ) 
VAR _table = SELECTCOLUMNS(
    TOPN(
        [Highlighted Number],
        ALL( Products ),
        [Total Sales],
        DESC
    ),
    "Name", Products[Product Name]
) 

RETURN
IF(
    SELECTEDVALUE( Products[Product Name] ) IN _table,
    "Red", BLANK()
)

[Parth_M]

Here is my submission for the workout. Thank You Enterprise DNA for the DAX Challenges.

Dax Code:

[rishug2811]

Late submission!
Thanks @Paul.Gerber and @EnterpriseDNA team for such great workouts

I have done it in two ways -

1. Using Calculated Column for ranks and then measure for color

Calculated Col-

Rank (Total Costs) = RANKX(Products, [Total Costs],ASC,Dense)
Rank (Total Sales) = RANKX(Products, [Total Sales],DESC,Dense)

Measures for color-

Color - TotalCosts =

VAR _RankTotalCosts = SELECTEDVALUE(Products[Rank (Total Costs)])
VAR _TopN = SELECTEDVALUE(‘Top & Bottom N’[Top & Bottom N])

return IF(_RankTotalCosts <= _TopN, “#FF0060”)

Color - TotalSales =

VAR _RankTotalSales = SELECTEDVALUE(Products[Rank (Total Sales)])
VAR _TopN = SELECTEDVALUE(‘Top & Bottom N’[Top & Bottom N])

return IF(_RankTotalSales <= _TopN, “#00DFA2”)

2. Using Measures to calculate rank and color in same measure.

TopNColored Costs =

VAR _SelectedRank = SELECTEDVALUE(‘Top & Bottom N’[Top & Bottom N])

VAR _Ranks = RANKX(ALL(Products),[Total Costs],ASC,Dense)

VAR _Color = IF(_Ranks <= _SelectedRank, “Red”)

RETURN _Color

TopNColored Sales =

VAR _SelectedRank = SELECTEDVALUE(‘Top & Bottom N’[Top & Bottom N])

VAR _Ranks = RANKX(ALL(Products),[Total Sales],DESC,Dense)

VAR _Color = IF(_Ranks <= _SelectedRank, “Green”)

RETURN _Color

My DAX Workout 006.pbix (558.7 KB)

[MEHMOOD.HASSAN.2111]

I reached the same solution.
But why is it not working if replace All(Products) with All(prouducts[Product Name])

[Paul.Gerber]

@MEHMOOD.HASSAN.2111 hello. Thanks for the question.

In Power BI, the behavior of a measure can change when you replace All(Products) with All(Products[Product Name]), depending on the context in which the measure is being used. Let me explain the difference between these two expressions:

1. All(Products):

• This expression removes all filters and context applied to the “Products” table. It effectively considers all the products in your dataset, regardless of any other filters or selections on the report. This is often used when you want to ignore any filters applied to the entire report for a specific measure.

2. All(Products[Product Name]):

• This expression specifically removes filters and context applied to the “Product Name” column within the “Products” table. It doesn’t remove filters or context on the entire “Products” table, only on the “Product Name” column.

Hope this helps.

Paul

[MEHMOOD.HASSAN.2111]

Just Trying to understand why result are different with All(‘Products’) and All(‘Products’[Product Name])

[mbraun]

answer:

DAX Workout No6 MB.pbix (1.3 MB)