Top % of products

DAX DAX Calculations
1

Hi all,

I’ve covered how Sam demonstrates the top 50/200/rest products by sales using a supportive table.
In case a need to get the top 5%/10%/20% of products by sales, can the same technique be effective? If so, how would the measure look like?

Or should I use something like this?

Top5% Products (€) = VAR ProductPercent =
VAR DistinctCountSKU =
SUMMARIZE ( ‘SalesPricelist’; ‘SalesPricelist’[SKU] )
VAR Result =
SUMX ( DistinctCountSKU; 1 )
RETURN
ROUNDDOWN ( Result * 0,05; 0 )
RETURN
CALCULATE (
[Sales];
FILTER (
VALUES ( ‘SalesPricelist’[SKU] );
RANKX ( VALUES ( ‘SalesPricelist’[SKU] ); [Sales];; DESC ) <= ProductPercent
)
)

2

@Thimios Try to adapt your code in this way:

Top 5 % Products = 
VAR TopPerc = 0.05
VAR AllProducts =
    ALL ( Products[ProductKey] )
VAR TotalProducts =
    COUNTROWS ( ALL ( Products[ProductKey] ) )
VAR N =
    ROUNDUP ( TotalProducts * TopPerc, 0 )
VAR TOPNProducts =
    TOPN ( N, AllProducts, [Total Sales], DESC )
VAR Result = 
    CALCULATE ( [Total Sales], KEEPFILTERS ( TOPNProducts ) )
RETURN
    Result
3

Hi @Thimios

If you want show TopN Sales and Totals like this:

image_2021-02-12_140739
image_2021-02-12_140739488×502 10 KB

I use two measures:
In the first one we will perform the calculation of the TopN Product dynamically, using CALCULATETABLE to avoid problems with the RANKX function with negative values.

Product TopN Percent = 
VAR _CountProduct = CALCULATE(DISTINCTCOUNT(Products[Index]),ALL(Products))
VAR _TopN = INT( DIVIDE(_CountProduct * [TopN Value],100))
VAR _RankProduct = 
IF (
    ISINSCOPE ( Products[Product Name] ) && HASONEVALUE ( Products[Product Name] ),
    RANKX (
        CALCULATETABLE ( VALUES ( Products[Product Name] ), ALLSELECTED ( Products[Product Name] ) ),
        [Total Sales]
    )
)

VAR Result =
SWITCH(
    TRUE(),
    _TopN = 0, [Total Sales],
    _RankProduct <= _TopN, [Total Sales]
)

Return Result

And
The second part will allow us that the total only takes into account the TopN records.

Display TopN =
IF (
    COUNTROWS ( VALUES ( Products[Product Name] ) ) = 1,
    [Product TopN Percent],
    SUMX ( VALUES ( Products[Product Name] ), [Product TopN Percent] )
)

Note: I use a What If parameter to allow the end user dynamic selection of Percent

1 Like
4

Thank you @jbressan,

I cannot understand though the [TopN Value] in the 3rd row of your measure.

5

Hi @Thimios [TopN Value] is a WHAT IF PARAMETERS for dynamic selection.

image_2021-02-12_160245

For manual selection, you can change the percentage you are looking for. Example 20% of best-selling products

VAR _TopN = INT( DIVIDE(_CountProduct * 20 ,100))

1 Like