Forum Discussion

klaraklara's avatar
klaraklara
Frequent Visitor
2 years ago
Solved

Create segments based on article ranking

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




Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      klaraklara
      Frequent Visitor
      2 years ago

      Hi Anonymous 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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.