Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have written the following RANKX DAX formula to rank States by current month value. The resulting table is below:
State ranking (asc) = RANKX(ALLSELECTED('Current_month_labourforce'),calculate(Sum(Current_month_labourforce[Current month value])),,ASC)
What I'd like to do is exclude 'Australia' from the ranking, but still include it in the table showing a blank under rank. I would also like Australia to always be at the bottom of the table. I've attempted 'ALLEXCEPT' formulas etc but that results in the total exclusion of 'Australia' from the table.
My State and territory field sits in a table called State. So, 'State'[State and territory]...
Any help greatly appreciated.
cheers
Solved! Go to Solution.
Hi @M_Aird
Here's an idea that should work without changing your existing tables:
State ranking (asc) = IF ( SELECTEDVALUE ( State[State and territory] ) <> "Australia", RANKX ( CALCULATETABLE ( ALLSELECTED ( State ), State[State and territory] <> "Australia" ), CALCULATE ( SUM ( Current_month_labourforce[Current month value] ) ), , ASC ) )
R = IF ( SELECTEDVALUE ( State[State and territory] ) = "Australia", COUNTROWS ( ALLSELECTED ( State ) ), [State ranking (asc)] )
Well that's one method anyway. You could also add a special flag to the State table to flag Australia as being excluded from the ranking.
Cheers,
Owen 🙂
Hi @M_Aird
Here's an idea that should work without changing your existing tables:
State ranking (asc) = IF ( SELECTEDVALUE ( State[State and territory] ) <> "Australia", RANKX ( CALCULATETABLE ( ALLSELECTED ( State ), State[State and territory] <> "Australia" ), CALCULATE ( SUM ( Current_month_labourforce[Current month value] ) ), , ASC ) )
R = IF ( SELECTEDVALUE ( State[State and territory] ) = "Australia", COUNTROWS ( ALLSELECTED ( State ) ), [State ranking (asc)] )
Well that's one method anyway. You could also add a special flag to the State table to flag Australia as being excluded from the ranking.
Cheers,
Owen 🙂
Thanks so much Owen - your solution was great and gave me the result I was after!
Cheers again,
Megan
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |