Dynamic Ranking for legend.

[Swam80]

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()

)

[Swam80]

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]

@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]

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]

@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.

[DallasBaba]

@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!