cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bornnaked
Regular Visitor

Another rankx question

Alright, @mods/devs first off this forum lets you writeup a post without signing in then deletes that post when you go to sign in after the prompt....kinda a bummer. 

 

I'm playing around with FiveThirtyEight's Bob Ross data set. What I would like to do is view top N themes by year or quarter so we could chart if there was any variation. However, my attempts to do so have rankx returning just the count, not the rank. E.G. if a theme occurred five times, the rank is five, if once the rank is 1, if 10 the rank is 10. The rank returned is the same whether rankx is set to rank descending or ascending. 

 

Sample input data: 

 

Date

TITLE

drawing_theme

event

1983-01-11

A WALK IN THE WOODS

BUSHES

1

1983-01-11

A WALK IN THE WOODS

DECIDUOUS

1

1983-01-11

A WALK IN THE WOODS

GRASS

1

1983-01-11

A WALK IN THE WOODS

RIVER

1

1983-01-11

A WALK IN THE WOODS

TREE

1

1983-01-11

A WALK IN THE WOODS

TREES

1

1983-01-11

MT. MCKINLEY

CABIN

1

1983-01-11

MT. MCKINLEY

CLOUDS

1

1983-01-11

MT. MCKINLEY

CONIFER

1

 

Function: 

 

/// the first rank is calculated as:
Rank = RANKX(
		ALL(bob_stack[drawing_theme]),
		CALCULATE(
			SUM(bob_stack[event])
		),, DESC
	)
/// the second rank is calculated as so:
Rank2 = RANKX(
		ALL(bob_stack[drawing_theme]),
		CALCULATE(
			SUM(bob_stack[event])
		)
	)

 

 

Results:

drawing_theme

sum(event)

Rank

Rank2

APPLE_FRAME

1

1

1

AURORA_BOREALIS

2

2

2

BARN

17

17

17

BEACH

27

27

27

BOAT

2

2

2

BRIDGE

7

7

7

BUILDING

1

1

1

BUSHES

120

120

120

 

It's also worth noting that the calculate doesn't do anything in this case either, I'm merely using it because this bloke had a working rankx and seemed to think it was important. 

 

I'm not sure how dynamic this calculation is so I will also mention that removal of the sum(event) column also has no effect. 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

@bornnaked 

I think you are running into trouble because you have to remove the context of episode or it gets in the way.

Ranking = 
CALCULATE (
    RANKX ( ALL ( bob_stack[drawing_theme] ), [Occurrances],, DESC, DENSE ),
    REMOVEFILTERS ( bob_stack[EPISODE] )
)

jdbuchanan71_0-1620596219968.png

I have attached my sample file for you to look at.

 

 

View solution in original post

1 REPLY 1
jdbuchanan71
Super User II
Super User II

@bornnaked 

I think you are running into trouble because you have to remove the context of episode or it gets in the way.

Ranking = 
CALCULATE (
    RANKX ( ALL ( bob_stack[drawing_theme] ), [Occurrances],, DESC, DENSE ),
    REMOVEFILTERS ( bob_stack[EPISODE] )
)

jdbuchanan71_0-1620596219968.png

I have attached my sample file for you to look at.

 

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.