Product segmentation against sales ranking

[klaraklara]

I have a sales table, a dim_calender and dim_product table in PowerBi.

I created segments based on rankings by creating a calculated table

Segments Table =

ADDCOLUMNS(

    SUMMARIZE(dim_Product, dim_Product[Article]),

    "Segments",

    VAR RankSales = RANKX(ALL(dim_Product[Article]), CALCULATE(SUM(Sales[Net Sales)),,DESC)

    RETURN

        IF(

            RankSales <= 20, "Top 10",

            IF(AND(RankSales > 20, RankSales <= 40), "20-40",

            IF(RankSales > 40, ">+40", BLANK())

        ))),

    "Total Net Sales", CALCULATE(SUM(Sales[Net Sales]), ALL('dim_calendar'))

)

And then I calculated Net Sales:

Total Net Sales Measure =

SUMX(

    'Segments Table',

    CALCULATE(

        SUM(Sales[Net Sales]),

        ALL('dim_Product'),

        dim_Product[Article] = EARLIER('Segments Table'[Article]),

        USERELATIONSHIP(dim_Calendar[Date_id], Sales[Date_id])

    )

)

When using this in my visualization I get all segments in the rows and net sales per segment as values. However when I add my date filter from dim_calender the net sales changes correctly accordingly but segments are not recalculated depending on the time period I choose. So top 10 is fixed based on no time filter. 

How can I make this work so that ranking and segmentations are depending on the time I select? 

Thanks
Klara

[v-xiandat-msft]

Hi @klaraklara ,

Below is my table1:

Below is my table2:

Below is my table3:

The following DAX might work for you:

Dynamic RankSales = 
VAR CurrentDateFilter = ALLSELECTED(dim_Calendar[Date])
RETURN
RANKX(
    FILTER(
        ALLSELECTED(Sales[Article]),
        CALCULATE(
            SUM(Sales[Net Sales]),
            FILTER(
                ALLSELECTED(dim_Calendar),
                dim_Calendar[Date] IN CurrentDateFilter
            )
        ) > 0
    ),
    CALCULATE(SUM(Sales[Net Sales])),
    ,
    DESC,
    Dense
)

Dynamic Segment = 
VAR RankSales = [Dynamic RankSales]
RETURN
    IF(
        RankSales <= 20, "Top 10",
        IF(
            AND(RankSales >= 20, RankSales <= 40), "20-40",
            IF(RankSales > 40, ">+40", BLANK())
        )
    )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[klaraklara]

Thank you for your support!! 🙂 That DAX works to create the measurements for ranking and segments. However, I want to group all articles within "top 20" together and see their total net sales. Since this is a measurements I can't add "Dynamic Segment" as rows in a visualization but only as a value. 


I want a table like this that changes depending on which time period I choose

[v-xiandat-msft · Accepted solution]

Hi @klaraklara ,

Below is my table:

The following DAX might work for you:

Measure = CALCULATE(SUMX('Table','Table'[Net sales]),ALLEXCEPT('Table','Table'[Segments]))


Measure 2 = CALCULATE(SUMX('Table','Table'[Net sales]),ALLEXCEPT('Table','Table'[Segments]))/CALCULATE(SUM('Table'[Net sales]),ALL('Table'))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.