DAX Ranking issue

[Anonymous]

Hello,

Hoping someone can help me with my ranking query. 

Here is some sample data, the desired column is what I want to achieve. Basically, ranking the [value] by unit and metric. [Value] is a calculated measure in 'master' which is effectively the columns numerator/denominator. [Metric] is a column in table 'metrics', [Unit] is a column in 'Lookup' and [Month] is a column in 'Dates'.  THe tables Metrics, Lookup and Dates all have a one to many relationship to Master.

Unit	Metric 1	Desired	Metric 2	Desired	Metric 3	Desired
1	5	1	10	1	50	3
2	10	2	25	3	20	2
3	15	3	20	2	10	1

I created a calculated column ('Rank column')which is working which ranks all my units by each metric and by each month. However, when I try to change the date range on my matrix visual, the 'rank column' sums (or averages - depending on which summarize I choose) across all those months. I want the rank not to average but to rank based on the actual [value] that is visible after my filtering of month. These is causing issues because if a unit is ranked one for 3 of the 4 months, and then 60 for the 4 months the average rank may not be reflective of the value across all the 4 months.

Here is my DAX for my current calculated column. 

RANKX(FILTER('Master', 'master'[Metric] = EARLIER('Master'[Metric]) && 'Master'[Date] = EARLIER('Master'[Date])),Master[%] ,,ASC,Skip),

Many thanks in advance

[123abc]

It seems like you're trying to create a calculated column to rank units by metric and month without summarizing the values across different months. Your current DAX formula is ranking units for a specific metric and date context but is not sensitive to the filter context set by your matrix visual. To achieve the desired result, you need to make your calculated column context-aware by taking into account the filters applied in your visuals. You can use the RANKX function along with the FILTER function.

You can modify your DAX formula for the 'Rank column' as follows:

Rank column =
VAR CurrentUnit = 'Master'[Unit]
VAR CurrentMetric = 'Master'[Metric]
VAR CurrentDate = 'Master'[Date]
VAR FilteredTable =
FILTER('Master',
'Master'[Unit] = CurrentUnit &&
'Master'[Metric] = CurrentMetric &&
'Master'[Date] = CurrentDate
)
RETURN
RANKX(FilteredTable, [Value],, ASC, Skip)

In this DAX formula:

1. We use the VAR statements to store the current Unit, Metric, and Date values from the 'Master' table for the row being evaluated.

2. We then use the FILTER function to create a context-aware table 'FilteredTable' that includes only the rows from the 'Master' table that match the current Unit, Metric, and Date.

3. Finally, we use the RANKX function on 'FilteredTable' to rank the rows based on the 'Value' column for the specific context. This will ensure that the ranking is based on the filtered values for the given Unit, Metric, and Date.

Now, when you change the date range on your matrix visual, the 'Rank column' will provide the ranking based on the actual filtered values for the selected months, rather than averaging or summing across all months.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.