Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Two Questions:
Period Category Item Price ($) Fractional Rank
May Bikes BikeX2 578 1
May Bikes BikeY6 560 2
May Bikes BikeN7 558 3.5
May Bikes Bike J9 558 3.5
May Bikes BikeD3 479 5
May Toys ToyF3 592 1
May Toys ToyK7 588 2
May Toys ToyW1 468 3
May Toys ToyE8 402 4.5
May Toys ToyR4 402 4.5
Jun Bikes BikeJ9 642 1.5
Jun Bikes BikeD3 642 1.5
Jun Bikes BikeN7 541 3
Jun Bikes BikeX2 506 4
Jun Bikes BikeY6 479 5
Jun Toys ToyW1 483 1
Jun Toys ToyF3 472 2.5
Jun Toys ToyR4 472 2.5
Jun Toys ToyE8 399 4
Jun Toys ToyK7 389 5
Solved! Go to Solution.
Hi @robarivas,
Don't know the answer to 1.
For 2, I'm assuming you want to do this in a calculated column.
You can calculate the Fractional Rank by calculating the Standard Competition ranking and adding 0.5 * (Num items with same price - 1).
Also, you can use ALLEXCEPT to calculate ranking within the current Period/Category group.
In DAX, this looks something like:
Fractional Rank = VAR NumTies = CALCULATE ( COUNT ( Price[Price] ), // Or COUNTROWS ( Price ) ALLEXCEPT ( Price, Price[Period], Price[Category], Price[Price] ) ) RETURN RANKX ( CALCULATETABLE ( Price, ALLEXCEPT ( Price, Price[Period], Price[Category] ) ), Price[Price] ) + DIVIDE ( NumTies - 1, 2 )
Here's a sample pbix file.
Owen 🙂
Hi @robarivas,
Don't know the answer to 1.
For 2, I'm assuming you want to do this in a calculated column.
You can calculate the Fractional Rank by calculating the Standard Competition ranking and adding 0.5 * (Num items with same price - 1).
Also, you can use ALLEXCEPT to calculate ranking within the current Period/Category group.
In DAX, this looks something like:
Fractional Rank = VAR NumTies = CALCULATE ( COUNT ( Price[Price] ), // Or COUNTROWS ( Price ) ALLEXCEPT ( Price, Price[Period], Price[Category], Price[Price] ) ) RETURN RANKX ( CALCULATETABLE ( Price, ALLEXCEPT ( Price, Price[Period], Price[Category] ) ), Price[Price] ) + DIVIDE ( NumTies - 1, 2 )
Here's a sample pbix file.
Owen 🙂
In divide function you have used NumTies ? What it is ?
That formaula/syntax worked. Thanks so much!!
Not aware of a way to do this with just the standard RANKX funtion. RANKX supports skip and dense for handling ties. Skip is the equivalent of your Standard. I would imagine it would involve a RANKX using standard for ties and then the Fractional ranking built off of this standard ranking.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |