The below Rank Window function doesn't work. Rank only gives me ones. I am trying to rank the measure "% Change Quarter" against 'Calendar'[Year] and 'Calendar'[Quarter]:
EVALUATE
SUMMARIZECOLUMNS(
'Calendar'[Year],
'Calendar'[Quarter],
"% Change Quarter", [% Change Quarter],
"RNK", rank(
DENSE,
SUMMARIZECOLUMNS(
'Calendar'[Year],
'Calendar'[Quarter],
"% Change Quarter", [% Change Quarter]
),
ORDERBY([% Change Quarter], DESC),
PARTITIONBY('Calendar'[Year])
)
)
Output Below:
|Calendar[Year]|Calendar[Quarter]| [% Change Quarter]|[RNK]
-----------------------------------------------------------
|2023 | Q2 |0.07 |1
|2023 | Q3 |-0.11 |1
|2023 | Q4 |0.57 |1
|2024 | Q1 |0.69 |1
|2024 | Q2 |-0.12 |1
|2024 | Q3 |0.01 |1
What am I missing, is there a problem with my filter context for year and Quarter? Does it need an All filter function, because I tried that with summarizecolumn but it is not working.
The below is the PBIX file: https://1drv.ms/u/c/1e7368b2a4fe3faa/EdliZDH7XtlItZYYIRZasBABiGHzsp-whGV-q1mqOW0KiQ?e=9bnnii
Does this do what you want?
EVALUATE
SUMMARIZECOLUMNS(
'Calendar'[Year],
'Calendar'[Quarter],
"% Change Quarter", [% Change Quarter],
"RNK", RANK(
DENSE,
CALCULATETABLE(SUMMARIZECOLUMNS(
'Calendar'[Year],
'Calendar'[Quarter],
"% Change Quarter", [% Change Quarter]
), REMOVEFILTERS('Calendar'[Year],'Calendar'[Quarter])),
ORDERBY(
[% Change Quarter],
DESC
),
PARTITIONBY('Calendar'[Year])
)
)