Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hiya all,
I've not posted before, apologies if this isn't in the right place.
I want to rank clubs by their YOY (year on year) variances - however there are some which may not have any sales this year or last year, in the period/category that has been chosen by the user in the slicer.
I am getting:
Club YOY Rank
------- -------- -------
Club 1 +£500 1
Club 2 +£200 2
Club 3 3
Club 4 -£100 4
YOY is a calculated measure.
This is what I have now :
YOY Rank = RANKX (ALL('Core ClubsVW'),[YOY])
How can I show this as :
Club YOY YOY Rank
------- -------- ----------
Club 1 +£500 1
Club 2 +£200 2
Club 4 -£100 3
Club 3
Please could someone help me?!
Thanks
Hannah
Solved! Go to Solution.
You may refer to DAX below.
YOY Rank = IF ( NOT ( ISBLANK ( [YOY] ) ), RANKX ( FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ), [YOY] ) )
You may refer to DAX below.
YOY Rank = IF ( NOT ( ISBLANK ( [YOY] ) ), RANKX ( FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ), [YOY] ) )
Thanks Sam.
Regards,
Madawa
Love this, thank you! Couldn't have done it without your help 🙂
I've amended it slightly so that it:
1) doesn't filter on selected (I want to show estate rank, even if you choose region in a slicer)
2) has a filter on open clubs
3) only shows if hasonevalue (so that it doesn't show a total at the bottom)
YOY Estate Rank = IF( HASONEVALUE('Core ClubsVW'[CostCentre]), IF ( NOT ( ISBLANK ( [YOY] ) ), RANKX ( FILTER ( ALL ( 'Core ClubsVW' ), NOT ( ISBLANK ( [YOY] ) ) && 'Core ClubsVW'[OpenFlag]=1), [YOY] ) ), "")
Thanks SO much.
Hannah
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |