Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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 |
---|---|
82 | |
79 | |
66 | |
49 | |
46 |
User | Count |
---|---|
103 | |
44 | |
39 | |
39 | |
39 |