RANKX is not working Create Rank against Month and Type

[DebbieE]

I want to be able to rank percentages that Im looking at against Month and Group. The following shows the info in a simple table (With the rank I want) but I will be putting it into a matrix with Month and Columns

Jan	Group 1	89%	1
Jan	Group 2	60%	2
Jan	Group 3	55%	3
Feb	Group 1	90%	2
Feb	Group 2	99%	1
Feb	Group 3	60%	3
Mar	Group 1	50%	3
Mar	Group 2	80%	1
Mar	Group 3	70%	2

I have used the following DAX but its ranking everything as 1

Rank =

RANKX ( ALLSELECTED ( 'dim '[Group] ), CALCULATE ( [Avg Overall satisfaction] ) )

Im wondering if anyone could point me in the right direction on this one?

[v-frfei-msft]

Hi @DebbieE ,

To create a measure as below.

Measure = RANKX(ALLEXCEPT('Table','Table'[month]),[Avg Overall satisfaction],,DESC,Dense)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[DebbieE]

I have added this in as rank2 and it doesnt seem to work  The DAX I used (Rank) works fine in a table (If I omit Null values)

Here is a picture of both ranks in action

Rank = RANKX(CROSSJOIN(ALL('dim Date'[date].[Month]),ALL('dim Exec Flash Report'[Group])),

[Avg Overall Satisfaction],,DESC)

Rank2 = RANKX(ALLEXCEPT('dim Date','dim Date'[date].[Month]),[Avg Overall Satisfaction],,DESC,Dense)

The issue is that Its ranking null values and because I am using a fact from the fact table and date from dim date time, there are lots of null values because (For instance) May doesnt have any values against it.

Neither of them work within a matrix

Unfortunately I cant upload my files to One Drive.

[DebbieE · Accepted solution]

Rank =

IF(

NOT ISBLANK( 'fact Exec Flash Report'[Avg Overall Satisfaction (Flash Report)]),

RANKX(CROSSJOIN(ALLSELECTED('dim Date'[date].[Month]),ALLSELECTED('dim Exec Flash Report'[Survey Type])),

[Avg Overall Satisfaction (Flash Report)],,DESC)

)

[Anonymous]

Try using :

rank_ = rankx(CALCULATETABLE(ALLEXCEPT(Table1,Table1[Month])),CALCULATE(sum(Table1[avg Overall Satisfaction])),,DESC)

[DebbieE]

Ive had a go

I cant SUM my AVG Overall Satisfaction. Parameter is not the correct type. Its already averaged in a measure so I changed it to this

Rank = RANKX(CALCULATETABLE(ALLEXCEPT('dim Date','dim Date'[date].[Month])),CALCULATE(sum('fact '[Overall satisfaction])),,DESC)

However its now looking like this

Which obviouslyisnt correct. I have filtered out Lots of NULL records. If I keep them in the values get even crazier.

I think I know why it is. My Month comes from a Date field in dim date time. My  Average comes from my fact table. The two are joined by a date key. There are many more dates in dim date than I have in the fact table

Dim Date                             Fact

Month                                89% 

March                                 90%

April

May

Jun

So it may be ranking against all the date rows without a fact. Im not sure how to resolve this issue

July

[DebbieE]

I have almost got it working

Rank = RANKX(CROSSJOIN(ALL('dim Date'[date].[Month]), ALL('dim Exec Flash Report'[Group])),

[Avg Overall satisfaction],,DESC)

Drag in Month In a table it almost works fine with a filter to omit NULL Facts. 

I do want the info in a matrix. I want to see all 12 months as columns and its assigning a rank to NULL values of 1. Is there any way for me to change this DAX query to only rank actual values?