Using RANKX Measure to Create a Rank Frequency Table

[Anonymous]

Hello

I have a task which I've been stuck on for some time and am wondering if is possible through Power BI.

I've created a basic example dataset which I hope will illustrate my requirements.

I start with a table with 4 columns:

• One date column
• Two category columns
• One value column

I then want to rank this data by date and one category, which I've managed to do using the RANKX function through the following formula:

Rank = 
RANKX(
ALLEXCEPT(
Table1,
Table1[Date],
Table1[Category1]),
CALCULATE(AVERAGE(Table1[Value1])),,DESC,DENSE)

to produce the following table:

My hope is that from this measure I am able to create a table showing the frequency of each category by rank. For example, the result using this data would look like the table below. This shows where Date Slicer = 01/01/2021. On this date, alpha was both rank 1 and 2 a frequency of 1 time, and beta was rank 1 a frequency of 1 time.

I have experimented with the DATATABLE function for this but I haven't come up with any result. 

If there is a way to do this and someone has experience, I would greatly appreciate the help.

Thanks 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try to create a calculated column:

Rank = 
RANKX(
FILTER('Table',EARLIER('Table'[category1])='Table'[category1]&&EARLIER('Table'[date])='Table'[date]),
'Table'[value1],,DESC,DENSE)

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

[lbendlin]

Please provide sample data in usable format (not as a picture).