Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am using AdventureWorks DB and I want to rank the resellers by their total sale amount. So basically the table looks like this:
I used the following measure to rank it:
Rank Reseller = RANKX(ALL(ResellerSales), MAX(ResellerSales[SalesAmount]),,1)
But it returns only 1 as the rank.
I
followed these two videos:
https://www.youtube.com/watch?v=z2qzJVeYhTY
and
https://www.youtube.com/watch?v=SsZseKOgrWQ&t=603s
but I can't understand what is wrong.
Can anyone explain, please 😞
Solved! Go to Solution.
@Anonymous
The Measure I posted above works in the file you shared!
In the File you uploaded you had the Reseller Key from the Reseller Sales table and that's why it works,
In the picture you posted above it did not work because you had the Reseller Key from the Reseller Table.
In that case you need to adjust the Measure to reference that table.
Rank Reseller 2 = RANKX ( ALLSELECTED ( Reseller[ResellerKey] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), ,)
@Anonymous
@Sean MEASURE works well in the file
But in the screen shot, i noticed that you are also including Reseller Name from another Table "Reseller"
If you include that other column, this MEASURE would work
Rank Reseller = RANKX ( SUMMARIZE ( ALLSELECTED ( ResellerSales ), ResellerSales[ResellerKey], Reseller[ResellerName] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), , )
Hi @Anonymous
try to wrap CALCULATE around your MAX function in order for context transition to happen
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Dear Livio
I think this, is not the solution. Because, by wrapping it with calculation, the result makes no sense, because when I sort the SalesAmount column to an Desc Order, the first one should have the Rank 1, but it has rank 41 !!
Why?
I believe you also may want to use SUM and not MAX in your formula ?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
How about this
Rank Reseller = RANKX ( ALLSELECTED ( ResellerSales[ResellerKey] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), , )
Hope this helps!
@Sean
Thank you for your answer. I have already tested that, and it is not working :
@Anonymous
The Measure I posted above works in the file you shared!
In the File you uploaded you had the Reseller Key from the Reseller Sales table and that's why it works,
In the picture you posted above it did not work because you had the Reseller Key from the Reseller Table.
In that case you need to adjust the Measure to reference that table.
Rank Reseller 2 = RANKX ( ALLSELECTED ( Reseller[ResellerKey] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), ,)
@Anonymous
@Sean MEASURE works well in the file
But in the screen shot, i noticed that you are also including Reseller Name from another Table "Reseller"
If you include that other column, this MEASURE would work
Rank Reseller = RANKX ( SUMMARIZE ( ALLSELECTED ( ResellerSales ), ResellerSales[ResellerKey], Reseller[ResellerName] ), CALCULATE ( SUM ( ResellerSales[SalesAmount] ) ), , )
@Zubair_Muhammad and @Sean and @LivioLanzo
Thank you so much for your amazing support.
Now, the measure is working. Let's cross our fingers that I can figure RANKx out for the next examples as well
@LivioLanzo
Basically, I have a reseller list and the saleAmount (which is the sale*quantity) for each reseller.
Now I want to have them ranked in a way that the reseller with the highest SaleAmount receives the rank 1.
I believe I should use neither MAX nor SUM. But the RANKx function didn't accept the columns without an aggregation function.
could you share your file? The measure depends on the model and what is being used in the matrix
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
You can find the pbix file in the following link:
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |