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 measures for metrics like Order count, Order quantity, LIFR%,VOFR%,OTIF%, etc. What I want to do is, plot a linechart of every metric with time (from date table) but only top N customers. 
So I have done this till now. Basically I created a table with metrics name and then Selected_Metric_Value will return the metric value when i select it in slicer. I have tried using RANKX but the lineplot does not show the legend of customer names properly.
For eg : If I select LIFR% on the slicer, I want the linechart to have top N customers lines vs timeframe. Then if I select Order count then I want the plot to dynamically change the data and legend both.

MetricsTable = DATATABLE(
    "Metric", STRING,
    {
        {"Order Count"},
        {"Order Lines Count"},
        {"Order Quantity"},
        {"LIFR%"},
        {"VOFR%"},
        {"OTIF%"}
    }
)

Selected_Metric_Value = SWITCH(
    TRUE(),
    SELECTEDVALUE(MetricsTable[Metric]) == "Order Count", [Total_orders],
    SELECTEDVALUE(MetricsTable[Metric]) == "Order Lines Count", [Total_order_lines],
    SELECTEDVALUE(MetricsTable[Metric]) == "Order Quantity", [Total_order_quantity],
    SELECTEDVALUE(MetricsTable[Metric]) == "LIFR%", [LIFR%],
    SELECTEDVALUE(MetricsTable[Metric]) == "VOFR", [VOFR%],
    SELECTEDVALUE(MetricsTable[Metric]) == "OTIF%", [OTIF%],
    BLANK()
)
 
legend
power bi
Ranks

5 Replies

  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    Swam80  you can use the RANKX function.

    First, create a new measure that calculates the metric value for each customer.

    Order Count per Customer = CALCULATE([Order Count], ALLSELECTED(Customers))

     Create another measure that ranks the customers based on the selected metric. 

    Customer Rank = RANKX(ALLSELECTED(Customers), [Order Count per Customer], , DESC)

    Create another measure that filters the data based on the selected metric and the top N customers. For example, if you want to filter the data based on the order count and the top 10 customers, you can create a measure like this

    Top N Customers Order Count = CALCULATE([Order Count], TOPN(10, ALLSELECTED(Customers), [Order Count per Customer]))

     

    Create a line chart with the date table on the x-axis, the selected metric on the y-axis, and the customer name on the legend. Then, add a visual-level filter to the chart that filters the data based on the customer rank and the top N customers.

     

    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

    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