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.
Hi all, I am trying to rank (from lowest to highest) a column as below
1.) Ranking of Products(ASC): Ranking the "Products" from highest to lowest
This is working -
Ranking of Products(ASC)= RANKX( ALLSELECTED(Table),CALCULATE(SUM('Table'[Products])))
2.)Ranking of cost(DESC): Ranking the "cost" only when "Ranking of Products(ASC)" = 1
This is not working -
Ranking of cost(DESC) = IF([Ranking of Products(ASC)]=1,RANKX( ALLSELECTED(Table),CALCULATE(SUM('Table'[cost])),,ASC))
Items | Products | Lowest Cost |
A | 5 | 12 |
B | 5 | 8 |
C | 5 | 5 |
D | 4 | 4 |
E | 2 | 7 |
Desired Outcome:
Items | Products | Cost | Ranking of Products(ASC) | Ranking of cost(DESC) | |
A | 5 | 12 | 1 | 3 | |
B | 5 | 8 | 1 | 2 | |
C | 5 | 5 | 1 | 1 | |
D | 4 | 4 | 3 | ||
E | 2 | 7 | 2 |
Could I have some advices on the second measure? Great thanks!
Solved! Go to Solution.
@ngct1112 , Try like
new Rank = RANKX( filter(ALLSELECTED(Table[Items],Table[Products]), Table[Products] =max(Table[Products])), CALCULATE(SUM('Table'[cost])),,ASC)
Ranking of cost(DESC) = IF([Ranking of Products(ASC)]=1,[new Rank ], blank())
Hi @ngct1112 ,
For the logical of DESC order and ASC order, it may be wrong in the original post. And I have correct it in the following formulas .
You may create measures like DAX below.
Ranking of Products(DESC) = RANKX( ALLSELECTED('Table'),CALCULATE(SUM('Table'[Products])), ,DESC,Dense)
Ranking of cost(ASC) = IF('Table'[Ranking of Products(DESC)]=1, RANKX( FILTER(ALLSELECTED('Table'), 'Table'[Ranking of Products(DESC)]=1),CALCULATE(SUM('Table'[cost])),,ASC,Dense))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ngct1112 ,
For the logical of DESC order and ASC order, it may be wrong in the original post. And I have correct it in the following formulas .
You may create measures like DAX below.
Ranking of Products(DESC) = RANKX( ALLSELECTED('Table'),CALCULATE(SUM('Table'[Products])), ,DESC,Dense)
Ranking of cost(ASC) = IF('Table'[Ranking of Products(DESC)]=1, RANKX( FILTER(ALLSELECTED('Table'), 'Table'[Ranking of Products(DESC)]=1),CALCULATE(SUM('Table'[cost])),,ASC,Dense))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If expression or value evaluates to BLANK it is treated as a 0 (zero) for all expressions that result in a number, or as an empty text for all text expressions.
If value is not among all possible values of expression then RANKX temporarily adds value to the values from expression and re-evaluates RANKX to determine the proper rank of value.
Optional arguments might be skipped by placing an empty comma (,) in the argument list, i.e. RANKX(Inventory, [InventoryCost],,,"Dense")
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
@ngct1112 , Try like
new Rank = RANKX( filter(ALLSELECTED(Table[Items],Table[Products]), Table[Products] =max(Table[Products])), CALCULATE(SUM('Table'[cost])),,ASC)
Ranking of cost(DESC) = IF([Ranking of Products(ASC)]=1,[new Rank ], blank())
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |