cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Flynnk31 Frequent Visitor
Frequent 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
SqlJason Member
Member

Re: RANKX on measure

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/

Flynnk31 Frequent Visitor
Frequent Visitor

Re: RANKX on measure

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

Sean Super Contributor
Super Contributor

Re: RANKX on measure

@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

Flynnk31 Frequent Visitor
Frequent Visitor

Re: RANKX on measure

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.

Sean Super Contributor
Super Contributor

Re: RANKX on measure

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?

 

Vvelarde Super Contributor
Super Contributor

Re: RANKX on measure

@Flynnk31

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Flynnk31 Frequent Visitor
Frequent Visitor

Re: RANKX on measure

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 Super Contributor
Super Contributor

Re: RANKX on measure

@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

Flynnk31 Frequent Visitor
Frequent Visitor

Re: RANKX on measure

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors