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.
Hello All,
I have a strange data set that is a table of line items, and have turned this into a virtual table for quote, user, and dealership views using a lot of DISTINCT() functions, which I believe may be the source of my frustration. I am trying to create a column of dealership ranks based on their total cost values.
I have created a measure that does this using:
Dealers - Rank = RANKX(ALL(Quotes[Dealer - Name]), [Dealer - Order Value])
Where
Dealer - Order Value = SUMX(DISTINCT(Quotes[Dealer - Name]), SUM([Cost]))
When I use this same formula in a Calculated Column, all Ranks are set to 1. In fact, ANY formula I've created for a Calculated Column applies Rank 1 to all rows. I've read this is likely because I'm using aggregate SUMs from Measures that don't work the same in Calulated Columns, but I have not figured out how to resolve this. The green text in Ranks is what I would like my column to look like if possible.
Below is a simplified example set of the data I am using, and what I need is a column ranking the Dealerships based on their Cost.
Line Item # | Quote # | Dealership | User | Cost | Rank |
1 | 55555 | Dealer 1 | Tom | $150 | 3 |
2 | 55555 | Dealer 1 | Tom | $200 | 3 |
3 | 55555 | Dealer 1 | Tom | $75 | 3 |
1 | 75364 | Dealer 2 | Jane | $50 | 4 |
1 | 96857 | Dealer 3 | Mike | $180 | 2 |
2 | 96857 | Dealer 3 | Mike | $350 | 2 |
1 | 63454 | Dealer 4 | Beth | $480 | 1 |
2 | 63454 | Dealer 4 | Beth | $540 | 1 |
3 | 63454 | Dealer 4 | Beth | $95 | 1 |
Any assistance with this matter would be greatly appreciated!
Thanks,
spham
Solved! Go to Solution.
I figured it out!
I was looking at the data the wrong way. I was trying to use a measure in a calculated column expression, which didn't throw a flag until I finally internalized what a calulated column is.. Instead of using a "quote cost" measure as the expression to SUM in RANKX, I needed to just use the Cost column, as there is no point to isolating the quote numbers distinctly when summing their cost value.
I don't know if this will help anyone else but I do appreciate this community very much!
For those interested, the function I wound up using is:
Dealer Ranks =
RANKX(
FILTER(
'Quotes',
'Quotes'[Distributor - Name]=EARLIER('Quotes'[Distributor - Name])
),
CALCULATE(
CALCULATE(SUM('Quotes'[Line Item $ - Calculated Extended]), 'Quotes'[Quote - Status]="Ordered", USERELATIONSHIP('Calendar'[Date], 'Quotes'[Quote - Order Date])),
ALLEXCEPT('Quotes', 'Quotes'[Dealer - Name])
),,,
DENSE
)
If anyone has any questions about the above DAX, let me know, as there are specifics that only make sense in the context of my data. The big revelations were using the FILTER+EARLIER combination to have sub category rankings, and the DENSE parameter at the end.
Thanks All,
spham
Hi @Anonymous ,
I would normally use a formula similar to below to rank a colum.
Sort =
RANKX (
VALUES ( 'Table'[Column] ),
CALCULATE (
MIN ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Column] )
),
,
ASC,
SKIP
)
Proud to be a Super User!
I figured it out!
I was looking at the data the wrong way. I was trying to use a measure in a calculated column expression, which didn't throw a flag until I finally internalized what a calulated column is.. Instead of using a "quote cost" measure as the expression to SUM in RANKX, I needed to just use the Cost column, as there is no point to isolating the quote numbers distinctly when summing their cost value.
I don't know if this will help anyone else but I do appreciate this community very much!
For those interested, the function I wound up using is:
Dealer Ranks =
RANKX(
FILTER(
'Quotes',
'Quotes'[Distributor - Name]=EARLIER('Quotes'[Distributor - Name])
),
CALCULATE(
CALCULATE(SUM('Quotes'[Line Item $ - Calculated Extended]), 'Quotes'[Quote - Status]="Ordered", USERELATIONSHIP('Calendar'[Date], 'Quotes'[Quote - Order Date])),
ALLEXCEPT('Quotes', 'Quotes'[Dealer - Name])
),,,
DENSE
)
If anyone has any questions about the above DAX, let me know, as there are specifics that only make sense in the context of my data. The big revelations were using the FILTER+EARLIER combination to have sub category rankings, and the DENSE parameter at the end.
Thanks All,
spham
Hi @Anonymous,
You can create one measure as below:
Rank = RANKX(ALL('Quotes'[Dealership]),CALCULATE(SUM(Quotes[Cost]),ALLEXCEPT(Quotes,Quotes[Dealership])),,ASC)
Best Regards
Rena
Hello Rena,
I appreciate the speedy response, but I currently have a measure that does this. I need a Calculated Column of these values so that I can create a group off of it for ranks 1-5, as I need a Top 5 group to apply to the legend of the Map visual.
Thanks again!
spham
For Rank Refer these links, if they can help
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
Hello @amitchandak ,
After finding my solution, I realized that there was a new issue that I would have, but your articles provided the solution! There are also two clients for the distributors, and we do need to do nested ranking between clients. The first article covered that beautifully!
I don't think I should accept it as an answer to this topic, but those resources are invaluable!
Thanks again,
spham
Hi @Anonymous ,
I would normally use a formula similar to below to rank a colum.
Sort =
RANKX (
VALUES ( 'Table'[Column] ),
CALCULATE (
MIN ( 'Table'[Value] ),
ALLEXCEPT ( 'Table', 'Table'[Column] )
),
,
ASC,
SKIP
)
Proud to be a Super User!
Hello @amitchandak ,
That is some great documentation, and I appreciate the support. However it is all information I've uncovered in the community here trying to solve this issue.
I am trying to build a calculated column, which only appears in the first of your 3 links, and I already have a measure that performs this function. According to everything I've read, a calculated column that returns the ranks I'm looking for should be something like:
Dealers - Ranked = RANKX(ALL(Dealers), SUMX(DISTINCT(Dealers), [Cost]))
However this returns all ranks as 1. Please let me know if there is anything else I can do!
Thanks,
spham
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |