Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Flynnk31
Regular Visitor

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 10
Sean
Community Champion
Community 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
Community Champion
Community Champion

@Flynnk31

 

Is this the result you are looking for? If it is...

 

Rank Measure = RANKX( ALLEXCEPT('Table', 'Table'[No_]), [Total Cost Measure] ,, DESC)

 

Rank Allexcept.png

 

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...

Rank Allexcept2.png

 

This should give you some ideas on how to approach this depending on your real data!

 

Hope this helps!

Good Luck! Smiley Happy

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?

 

MissingRank1.PNGRank1.PNG

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)

 

a4.PNG

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SqlJason
Memorable Member
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/

Sean
Community Champion
Community Champion

@Flynnk31

 

I think its your Total Cost Measure causing the problems - change it to just this....

 

Total_Cost = SUM(DaysToConsumption[TotalCost])

Good Luck! Smiley Happy

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.

@Flynnk31

 

What happen if you remove the Qty Column of the visual?




Lima - Peru

The only Sort By on that column is itself "No_ (Default)".

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.