topic Help with Ranking in DAX Commands and Tips

Help with Ranking

Anonymous — Tue, 22 Sep 2020 16:16:08 GMT

Hi!

I'm looking for help with the ranking function:
I am looking for the DAX formula to rank Stores depending on the Value and within the same Cluster.
I have one table with Clusters and corresponding Stores in column; the Value is a calculated measure

For example:
- I have 5 Stores with their respective Cluster
- the same visual contains all Clusters (not filtered with any slicer)
- my current formula calculated the ranking on all stores :
Ranking = RANKX((ALLSELECTED(Stores));Value)

Here is the result:

Cluster	Store	Value	Ranking
A	a	5	5
A	b	10	4
B	c	50	1
C	d	30	2
C	e	20	3

Here is what I'm looking for:

Cluster	Store	Value	Ranking
A	a	5	2
A	b	10	1
B	c	50	1
C	d	30	1
C	e	20	2

How do I get the ranking by Cluster?

Thank you very much!

Re: Help with Ranking

Greg_Deckler — Tue, 22 Sep 2020 15:49:40 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?

Maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Help with Ranking

AntrikshSharma — Tue, 22 Sep 2020 16:31:50 GMT

Anonymous Try this, file is attached below my signature:

Measure = VAR OuterCluster = SELECTEDVALUE ( Ranking[Cluster] ) VAR OuterValue = [Total Value] VAR TempTable = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Ranking, Ranking[Cluster], Ranking[Store] ), "Val", [Total Value] ), ALL () ) VAR FilterRows = FILTER ( TempTable, Ranking[Cluster] = OuterCluster && [Val] >= OuterValue ) VAR Result = COUNTROWS ( FilterRows ) RETURN Result