dax | ranking by a measure to a monthly list

[hood2media]

hi,

apprec any help to create a ranking no (1,2,3,4.....) to the table below:

the month is taken from the calendar table while the 'total crime' measure is simply calculated as follows:

COUNTROWS( CrimeData )

i tried using following dax formula but it gives my just '1' for all months

RANKX(
ALL( 'Calendar'[Month] ),

[Total Crime], ,

DESC,

Dense)

note:
i also intend to do similar table to rank the 'total crime' measure by day of week. 
tks & krgds, -nik

[v-yiruan-msft · Accepted solution]

@manvishah17 Thanks for your contribution on this thread.

Hi @hood2media,

I have created a sample PBIX file and applied the same formula as yours to the visual. However, it can return the expected result.I am uncertain about the cause of this issue. Could you kindly provide some sample data from the ‘CrimeData’ table in Text format (excluding any sensitive information)? This would greatly assist in identifying the problem and finding a solution. For instructions on how to share the required information, please refer to the following link: 

How to provide sample data in the Power BI Forum

Best Regards 

[manvishah17 · Accepted solution]

See I also created sample pbix file , and also tried to implement weekday wise rank. 
Hope this help. 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manvishah17 · Accepted solution]

See I also created sample pbix file , and also tried to implement weekday wise rank. 
Hope this help. 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hood2media]

tks @manvishah17 & @v-yiruan-msft .

as i'm still on the road / travelling now, pls allow me some time to chk/respond on this later.

krgds, -nik

[hood2media · Accepted solution]

dear @manvishah17 & @v-yiruan-msft,

pursuant to the earlier responses, i hv found out tht the reason for the inability to get the ranking to b shown properly was bcoz of the sorting order for the month (sorted by monthno) & day name (by dayno) - all of which were initially created in the calendar table thru power query.

hvg flwed the solution fm the flwg webpage, i hv managed to resolve the issue:

Ranking by Month & Year not working with RANKX - DAX / DAX Calculations - Enterprise DNA Forum

i hv also appended blw, the calendar table + the correct report:

 ---

---

many tks again to both of u fr yr suggestions. they r most apprec.

krgds, -nik  

[v-yiruan-msft · Accepted solution]

@manvishah17 Thanks for your contribution on this thread.

Hi @hood2media,

I have created a sample PBIX file and applied the same formula as yours to the visual. However, it can return the expected result.I am uncertain about the cause of this issue. Could you kindly provide some sample data from the ‘CrimeData’ table in Text format (excluding any sensitive information)? This would greatly assist in identifying the problem and finding a solution. For instructions on how to share the required information, please refer to the following link: 

How to provide sample data in the Power BI Forum

Best Regards 

[manvishah17]

Yes 'sheet 1' is my fact table having all crimes number and dates of them occuring . 
Can you tell me more about your error.

[manvishah17]

Use this measure for your desired output.

Rank = RANKX(

    ALL(Sheet1),

    [TotalCrime],

    ,

    DESC

)

[hood2media]

hi @manvishah17 ,
is 'sheet1' your fact_table which in my case is 'CrimeData'?
i hv tried that for my 'CrimeData' table but it stil doesn't give the desired result.
krgds, -nik