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'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
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
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!
Almost @Sean, but thank you!
I actually got it exactly where I want it to be, except now it's just missing Rank 1 in the live data, where in my data from Excel (same exact rows, just pasted from SQL). Any idea why that would happen?
Hi @Flynnk31,
As you rank values in No_ groups, please use the matrix instead of table visual to display rank values. You can place No_column in Row group of matrix. And have you tried the Rank Measure suggested by @Sean?
Rank Measure = RANKX( ALLEXCEPT('Table', 'Table'[No_]), [Total Cost Measure] ,, DESC)
Best Regards,
Qiuyun Yu
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/
I think its your Total Cost Measure causing the problems - change it to just this....
Total_Cost = SUM(DaysToConsumption[TotalCost])
Good Luck!
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.
The only Sort By on that column is itself "No_ (Default)".
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |