Forum Discussion
RANKX on measure
Hi All,
I'm relatively new to PowerBI and moreso DAX. I am having an issue trying to do a RANKX on a "Total_Cost" measure within one of my reports. So what I have right now is a list of items per lot (can have many lots per item), and I need to group by the item (No_ field) for the Total Cost (Total_Cost measure). So this is what I have for that measure:
Total_Cost = SUMX(ALL(DaysToConsumption[No_]), CALCULATE(SUM(DaysToConsumption[TotalCost])))
What I want to do is now rank all of the ItemNumbers by that Total_Cost, and I can't get it to behave like I would expect it to. I have tried a lot of different ways to write it, but I think this should be the correct way to find the Rank:
Rank = RANKX(ALL(DaysToConsumption[No_]), [Total_Cost], , DESC)
However, when I do this, everything gets a rank of 1. So then I tried this:
Rank = RANKX(ALL(DaysToConsumption), [Total_Cost], , DESC)
But that brings back even a more confusing Rank for each:
No_ Total_Cost Rank Total_Qty 109906 $1,401,983.21 1 348939.648 101298 $1,039,526.08 1 241073.01 107829 $916,293.34 1 235119.623 109337 $484,197.69 1 121738.012 110850 $484,197.69 1 121738.012 F107610 $392,978.66 1 86440 109318 $379,841.68 1 4498.885 101348 $348,141.92 1 77950 101852 $335,478.74 1 875350 110952 $326,432.63 1 125684.832 103841 $266,281.98 1 616015.685 103848 $266,281.98 1 616015.685 106148 $255,378.16 1 77601.92 110139 $246,321.39 1 48170.51 103780 $222,191.07 1 63100 111281 $202,007.97 1 40300.088 108809 $195,752.24 1 198462.05 110872 $185,024.04 2 13350 101263 $171,266.72 3 37037.28 110693 $167,599.50 4 56051.955 109761 $167,092.20 4 1212.53 101553 $162,654.00 5 126005.523 108747 $162,346.68 5 31569.872 110301 $142,667.00 14 19950 F107608 $139,596.86 15 33680 108117 $138,499.44 15 13221.788 110986 $127,576.03 16 5850 106814 $112,364.99 19 40564.64 109964 $111,521.14 20 260000 108825 $110,496.44 20 16950 107043 $106,046.08 27 5297.8 106534 $100,274.78 32 112000
Any ideas would be very helpful!
Thanks
10 Replies
- SqlJason
Memorable Member
Do you have a sort by column for DaysToConsumption[No_]? If yes, that also has to be passed in the RANKX like said in the article below -
http://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/
The only Sort By on that column is itself "No_ (Default)".
Thanks for the help!
So I changed the Total_Cost to:
Total_Cost = SUM(DaysToConsumption[TotalCost])
And Rank to:
Rank = RANKX(ALL(DaysToConsumption), [Total_Cost], , DESC)
but it is still giving the same results, not sure what else could be throwing this off.
SeanCommunity Champion
So what I have right now is a list of items per lot (can have many lots per item), and I need to group by the item (No_ field) for the Total Cost (Total_Cost measure).
Can you post some sample data showing all columns involved?
Ok, so I just tested this out with a smaller dataset with only the columns that are necessary (see bottom for data).
I was able to get this to do exactly what I wanted with these 2 measures:
Total_Cost = SUM(data2[TotalCost]) Rank = RANKX(ALL(data2[No_]), data2[Total_Cost], , DESC)
So I'm guessing I just need to go through my original dataset, and start fresh as I have a lot of measures that I was testing and unnecessary in it currently. I'll post back when I figure out the solution!
No_ Quantity lotNo Days TotalCost 101251 7605.87 100XD57 9 $26,240.24 101251 826.725 100B9S0 111 $2,521.51 101251 11519.035 1009CGD 81 $35,133.03 101288 2500 01/05/2017 58061475 2 $1,183.88 101288 1021.8 11/01/2016 58056885 5 $407.10 101288 2500 01/05/2017 58061478 2 $1,183.88 101288 2500 01/05/2017 58061447 3 $1,183.88 101288 2500 01/05/2017 58061450 3 $1,183.88 101288 2500 12/01/2016 58058793 11 $1,096.50 101288 2500 12/06/2016 58059182 9 $1,096.38 101288 2500 01/05/2017 58061454 3 $1,183.88 101288 2500 01/05/2017 58061448 3 $1,183.88 101288 2500 12/06/2016 58059180 5 $1,096.38 101288 2500 12/06/2016 58059181 9 $1,096.38 101288 2500 01/05/2017 58061476 2 $1,183.88 101288 2500 12/06/2016 58059186 9 $1,096.39 101288 2500 01/05/2017 58061453 2 $1,183.88 101288 2500 01/05/2017 58061477 2 $1,183.88 101288 2500 01/05/2017 58061452 2 $1,183.89 107829 19.148 HZN15047E 86 $72.84 107829 1873.91 HZN16003C 11 18 $8,258.80 107829 41226.02 HZN16004B 60 $158,355.29 107829 41226.02 HZN16004C 63 $158,355.24 107829 1873.91 HZN16003C 4 15 $8,258.80 107829 19 HZN15047E 86 $72.28 107829 1873.91 HZN16003C 5 15 $8,258.80 107829 1873.91 HZN16003C 3 13 $8,258.79 107829 1873.91 HZN16003C 8 16 $8,258.79 107829 0.962 HZN15047E 53 $3.66 107829 41226.02 HZN16003A 31 $157,455.53 107829 41226.02 HZN15047F 96 $156,940.24 107829 1873.91 HZN16003C 16 17 $8,258.80 107829 1873.91 HZN16003C 12 18 $8,258.80 107829 826.725 HZN16003C 1 13 $3,643.59 107829 1873.91 HZN16003C 2 13 $8,258.79 107829 1873.91 HZN16003C 15 18 $8,258.79 107829 15.038 HZN15047E 86 $57.21 107829 1873.91 HZN16003C 7 15 $8,258.79 107829 1873.91 HZN16003C 13 18 $8,258.80 107829 1873.91 HZN16003C 14 18 $8,258.80 107829 1873.91 HZN16003C 9 18 $8,258.79 107829 41226.02 HZN16003D 58 $157,455.53 107829 1873.91 HZN16003C 6 15 $8,258.80 107829 1873.91 HZN16003C 10 18 $8,258.80
- Sean
Is this the result you are looking for? If it is...
Rank Measure = RANKX( ALLEXCEPT('Table', 'Table'[No_]), [Total Cost Measure] ,, DESC)EDIT: So you can also try something like this...
Rank Measure = IF ( HASONEVALUE ( 'Table'[lotNo] ), RANKX ( ALL ( 'Table'[lotNo] ), [Total Cost Measure],, DESC ), IF ( HASONEVALUE ( 'Table'[No_] ), RANKX ( ALL ( 'Table'[No_] ), [Total Cost Measure],, DESC ) ) )And the results...
This should give you some ideas on how to approach this depending on your real data!
Hope this helps!
Good Luck! :smileyhappy:
