Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Solved! Go to Solution.
I'd recommend using the RANK function instead of RANKX. It's easier to sort by multiple things.
https://www.sqlbi.com/articles/introducing-the-rank-window-function-in-dax/
Your case might look something like this:
USD_Scrap_Rank =
VAR SourceTable =
CALCULATETABLE (
SUMMARIZE (
'Analysis EXCEL',
'Analysis EXCEL'[NCR Item No],
'DateTable'[Year],
'DateTable'[Month],
"@Sum", [Sum_Of_USD_Scrap]
),
ALLSELECTED ( 'Analysis EXCEL'[NCR Item No] ),
ALLSELECTED ( 'DateTable' )
)
VAR Result =
RANK (
DENSE,
SourceTable,
ORDERBY (
'DateTable'[Year], ASC,
'DateTable'[Month], ASC,
[@Sum], ASC
)
)
RETURN
Result
You need to include the month in the allselected section; Here's my revised DAX:
Scrap Rank = RANKX(
ALL(Data[Month], Data[NCR ITEm]),
CALCULATE(SUM(Data[USD Scrap]))
)
You can tweak it as needed.
BTW - i just rounded the scrap amounts to the lowest 100, so that's why there's ties in my screenshot.
hmm... Not sure if I implemented your solution correctly?
Hmmm this is quite interesting and complex... this is not the cleanest of solutions, but consider the following:
You can create a "helper" column that indexes your months, where your starting month index is 0. Then consider the following measure:
USD_Scrap_Rank =
VAR _Rank =
RANKX (
ALLSELECTED ( 'Analysis EXCEL'[NCR Item No] ),
[Sum_of_USD_Scrap],
,
ASC
)
VAR _Index =
'Table'[IndexHelperColumn] * 6
RETURN
_Rank + _Index
Essentially, this will add the continuous counting of your rank, like how you wanted in your inner rank.
Proud to be a Super User! | |
I'd recommend using the RANK function instead of RANKX. It's easier to sort by multiple things.
https://www.sqlbi.com/articles/introducing-the-rank-window-function-in-dax/
Your case might look something like this:
USD_Scrap_Rank =
VAR SourceTable =
CALCULATETABLE (
SUMMARIZE (
'Analysis EXCEL',
'Analysis EXCEL'[NCR Item No],
'DateTable'[Year],
'DateTable'[Month],
"@Sum", [Sum_Of_USD_Scrap]
),
ALLSELECTED ( 'Analysis EXCEL'[NCR Item No] ),
ALLSELECTED ( 'DateTable' )
)
VAR Result =
RANK (
DENSE,
SourceTable,
ORDERBY (
'DateTable'[Year], ASC,
'DateTable'[Month], ASC,
[@Sum], ASC
)
)
RETURN
Result
That worked! Thank you for your wisdom AlexisOlson!
User | Count |
---|---|
63 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |