topic Re: Dynamic Ranking for legend. in DAX Commands and Tips

Dynamic Ranking for legend.

Swam80 — Fri, 26 Jan 2024 19:09:22 GMT

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

)

Re: Dynamic Ranking for legend.

DallasBaba — Fri, 26 Jan 2024 19:38:17 GMT

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!

Re: Dynamic Ranking for legend.

Swam80 — Fri, 26 Jan 2024 19:44:58 GMT

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.

Re: Dynamic Ranking for legend.

DallasBaba — Fri, 26 Jan 2024 20:19:59 GMT

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!

Re: Dynamic Ranking for legend.

Swam80 — Sat, 27 Jan 2024 08:50:36 GMT

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

Re: Dynamic Ranking for legend.

DallasBaba — Sat, 27 Jan 2024 19:53:57 GMT

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.