cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shan_Drex12
Helper I
Helper I

Ranking question error

HI All,

 

I'm trying to do a ranking based on the highest accumulated days for detention.

 

I have the formula below, but the ranking seems to be out of order:

Rank = RANKX(ALL(Sheet1[Material Description]), CALCULATE(COUNT(Sheet1[# of Days in Detention])),,DESC)
 

rank.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@Shan_Drex12 , Make sure # of Days in Detention is  COUNT(Sheet1[# of Days in Detention]). it should NOT be un/not summarized

 

Formula seems correct

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @Shan_Drex12,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
sanalytics
Responsive Resident
Responsive Resident

Hello @Shan_Drex12 

Below meaures can work for you

 

sanalytics_0-1609251601863.png

 

regards,

sanalytics

amitchandak
Super User IV
Super User IV

@Shan_Drex12 , Make sure # of Days in Detention is  COUNT(Sheet1[# of Days in Detention]). it should NOT be un/not summarized

 

Formula seems correct

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Hi @amitchandak, if I don't use the 'don't summarize' table it messes up with the number of detention days I have. Inside of the query editer I did the difference between two dates. Also, for some reason I keep getting an error every time I try to share sample data, sorry I couldn't upload!

I used this formula:  inside of the power query to get the days detention by using this formula: Duration.Days(Duration.From([#"Today's Date"]-[Trailer Check In Time])) - 2

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors