Create segments based on article ranking

[klaraklara]

Hello!

I have three different tables with relationship to eachother in PBI
SALES: Articles (article_id), Net Sales and date_id (and more)
DIM_product: Artcile (article_id) specific attributes
Dim_Calender: date_id, weeks, months, year etc. 

I want to create a visulatization loking like this:

So I want to create a ranking of my articles based on their sales in the given time period that I filter on. Then I want to group together the top 20 best selling articles into segment "Top 20" and so on. 

In my current solutuion I do not manage to create segments that updates depending on time filter - they are fixed with no time filter so I cannot use them with dim_calender. 

How can I solve my problem?

All best,
Klara

[Ashish_Mathur · Accepted solution]

Hi,

Share the download link of the PBI file.

[v-jialongy-msft]

Hi @klaraklara

Please try the following steps:

Step 1: Create a Ranking Measure

First, you need to create a measure that ranks your articles based on net sales. This measure will dynamically update as filters are applied.

Article Rank = RANKX(ALL('SALES'[Articles]), CALCULATE(SUM('SALES'[Net Sales])), , DESC, Dense)

Step 2: Create a Segmentation Measure

Next, create a measure to segment your articles based on their ranking.

Article Segment = 
VAR ArticleRank = [Article Rank]
RETURN
    SWITCH(TRUE(),
        ArticleRank <= 20, "Top 20",
        ArticleRank <= 40, "21-40",
        ArticleRank <= 60, "41-60",
        "Other"
    )

Step 3: Use the Segmentation in Visualizations.









Best Regards,
Jayleny
If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[klaraklara]

Hi @v-jialongy-msft and thank you for replying!

These measures works correctly. The problem is the visualization. If creating a matrix table I cannot place "Article Segment" in rows since it is a measure and not a column. So I can't group all values in 1-20 segments together and reach my goal visualization I shared.

How do I solve that?

[v-jialongy-msft]

Hi @klaraklara 

If the matrix is not your only option, you can try using other visual objects to present your data more intuitively.

Best Regards,

Jayleny

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