Forum Discussion

Swam80's avatar
Swam80
Frequent Visitor
2 years ago

Dynamic Ranking for legend.

Hello all,   So I have been working on a powerbi project and I am new to this so just exploring. I have orders table, customers table,date table along with few other tables and I have created measu...
legend
power bi
Ranks
Swam80's avatar
Swam80
Frequent Visitor
2 years ago

Hey,

the thing is , I will have to do it for every metric.
I have one measure 

Top_Ranks = RANKX(ALL(dim_customers[customer_name]), [Selected_Metric_Value], , DESC,Dense)
So this will take whatever metric i selected in the slicer and rank it. I am lost after this. 
Because if I use a table visual, with values as top_ranks and rows as customer name. it gives proper ranks for every customer. But in lineplot, when I use customer name in the legend, it messes up.
  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    Swam80 Okay,  instead of using a separate measure for each metric, you can create a more generic solution that dynamically adapts to the selected metric.

    Rank_Per_Customer = 
RANKX(
    ALL(dim_customers[customer_name]), 
    [Selected_Metric_Value], 
    , DESC, Dense
)

Top_N_Customers_Metric = 
CALCULATE(
    [Selected_Metric_Value],
    FILTER(
        ALL(dim_customers[customer_name]),
        [Rank_Per_Customer] <= [N]
    )
)

    In the above code, [N] is a parameter that you need to define based on the top N customers you want to display in the line chart. You can set this parameter in your model or use a slicer to dynamically change it.

    Now, you can use the Top_N_Customers_Metric measure in your line chart. Configure the line chart as follows:

    • X-axis: Date from the date table.
    • Y-axis: Top_N_Customers_Metric.
    • Legend: Customer Name.

    With this setup, the line chart should dynamically adjust based on the selected metric in the slicer. You only need to create the Rank_Per_Customer and Top_N_Customers_Metric measures once, and they will work for any metric selected.

     

     Let me know if this works for you. @ me in replies, or I'll lose your thread!!!  
    Note:
    If this post is helpful, please mark it as the solution to help others find it easily. Also, if my answers contribute to a solution, show your appreciation by giving it a thumbs up! 
    • Swam80's avatar
      Swam80
      Frequent Visitor
      2 years ago

      Nope, didnt work. IT gives all customers same value.
      I tried one manual alternative, date on x axis, Selected_Metric_Value on y axis, legend as customer name. AND on customer_name filter I did topN filter option, so I set top 5 and in "by" option i used Selected_Metric_Value. It works.

      But I wanted to know if using measures canwe do it. Like I want to apply a Parameter slicer so that I can vary the N in top N. Now i have manually set N as 5

      • DallasBaba's avatar
        DallasBaba
        Skilled Sharer
        2 years ago

        Swam80 Let's modify the measures to make it work more dynamically with a parameter slicer.

         

        You can create a parameter table to store the value of N and then reference that parameter in your measures.

        ParameterTable = DATATABLE(
    "Parameter", STRING,
    {{"TopN", "5"}}  -- Set the default value of N here
)

        Modify the Rank_Per_Customer measure to use the parameter:

        Rank_Per_Customer = 
RANKX(
    ALL(dim_customers[customer_name]), 
    [Selected_Metric_Value], 
    , DESC, Dense
)

        Modify the Top_N_Customers_Metric measure to reference the parameter:

        Top_N_Customers_Metric = 
CALCULATE(
    [Selected_Metric_Value],
    FILTER(
        ALL(dim_customers[customer_name]),
        [Rank_Per_Customer] <= VALUES(ParameterTable[TopN])
    )
)

        Now, you can use the ParameterTable[TopN] in a slicer to dynamically change the value of N. This way, you only need to adjust the parameter to control the top N customers in your line chart.

         

        Ensure that the ParameterTable[TopN] column format is set to Whole Number in the Power BI model for correct filtering behavior.

         

        I hope this provides the flexibility you want, allowing you to vary the N using a parameter slicer.