Forum Discussion
KPI per Scenario (Delta)
Hello all,
I am struggleing with following issue:
I have this measure:
Financial Value =
VAR SelectedHeader=SELECTEDVALUE(cat1[Category1])
VAR GM =CALCULATE([_Value],
REMOVEFILTERS(cat1),
cat1[Category1]="Sales"
||cat1[Category1]="COGS"
)
VAR GM_=CALCULATE([GM%],
REMOVEFILTERS(cat1),
cat1[Category1]="Sales"
||cat1[Category1]="COGS"
)
VAR OI=CALCULATE([OI],
REMOVEFILTERS(cat1),
cat1[Category1]="Sales"
||cat1[Category1]="COGS"
)
VAR OI_=CALCULATE([OI%],
REMOVEFILTERS(cat1),
cat1[Category1]="Sales"
||cat1[Category1]="COGS"
)
VAR Result=
SWITCH(TRUE(),
SelectedHeader="GM",GM,
SelectedHeader="GM%",GM_,
SelectedHeader="OI",OI,
SelectedHeader="OI%",OI_,
[_Value]
)
return ResultIn a Matrix, I have also the Scenario (Actual, Budget). Now, I would like to get the Prior Year (I guess, I can use the measure with "Dateadd") and also a Delta column between PY, Actual and Budget. How can I get this delta?
Hi Pfoster ,
You're absolutely right that calculated columns operate in row context, and that can make RANKX behavior a bit confusing at first.
In your example:
FY Order = RANKX( 'Dim Date', 'Dim Date'[FY], , DESC, Dense )Here’s what’s happening:
- Even though you're inside a calculated column (which uses row context), RANKX iterates over the entire 'Dim Date' table because you explicitly passed 'Dim Date' as the first argument.
- The expression 'Dim Date'[FY] is evaluated for each row in that table.
- Since you're not applying any filters inside RANKX, it ranks all distinct FY values across the entire table.
- Because you're using Dense ranking, all rows with the same FY (e.g. 2025-2026) get the same rank (1), and the next distinct FY (e.g. 2024-2025) gets rank 2, and so on.
So even though there’s no context transition, RANKX is working as expected because it’s evaluating the full table and ranking the FY values globally.
If you wanted to rank within a group (like per year or quarter), you’d need to wrap the first argument in a FILTER to define that group context.
Let me know if you want help adjusting the logic to rank within a specific group.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
3 Replies
Hi Pfoster ,
You're absolutely right that calculated columns operate in row context, and that can make RANKX behavior a bit confusing at first.
In your example:
FY Order = RANKX( 'Dim Date', 'Dim Date'[FY], , DESC, Dense )Here’s what’s happening:
- Even though you're inside a calculated column (which uses row context), RANKX iterates over the entire 'Dim Date' table because you explicitly passed 'Dim Date' as the first argument.
- The expression 'Dim Date'[FY] is evaluated for each row in that table.
- Since you're not applying any filters inside RANKX, it ranks all distinct FY values across the entire table.
- Because you're using Dense ranking, all rows with the same FY (e.g. 2025-2026) get the same rank (1), and the next distinct FY (e.g. 2024-2025) gets rank 2, and so on.
So even though there’s no context transition, RANKX is working as expected because it’s evaluating the full table and ranking the FY values globally.
If you wanted to rank within a group (like per year or quarter), you’d need to wrap the first argument in a FILTER to define that group context.
Let me know if you want help adjusting the logic to rank within a specific group.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
