Forum Discussion

Pfoster's avatar
Pfoster
Icon for Resolver I rankResolver I
1 year ago
Solved

P&L KPI per Scenario

I am currently struggeling by adjusting a report of an older report. Following issue:
We have created a measure for showing some KPIs needed for a P&L:

"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 Result"

Now, we would like to show this measure for Prior Year (PY) and Budget (Bdgt), too. And also the deltas between Actual, PY and Bdgt. 
Is it possible to enlarge the current measure to get a matrix showing the expected outcome? 
Need Help
Show and Tell
Tips and Tricks
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi Pfoster  Yes, you can extend your measure to include Prior Year (PY), Budget (Bdgt), and their deltas. Use additional calculations like PY (via SAMEPERIODLASTYEAR) and filter Budget data with 'Scenario'[Scenario] = "Budget". Compute deltas (Actual - PY, Actual - Bdgt) and percent variances (e.g., (Actual - PY) / PY). Update your SWITCH logic to include these new metrics for dynamic selection in the matrix.

1 Reply

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    Hi Pfoster  Yes, you can extend your measure to include Prior Year (PY), Budget (Bdgt), and their deltas. Use additional calculations like PY (via SAMEPERIODLASTYEAR) and filter Budget data with 'Scenario'[Scenario] = "Budget". Compute deltas (Actual - PY, Actual - Bdgt) and percent variances (e.g., (Actual - PY) / PY). Update your SWITCH logic to include these new metrics for dynamic selection in the matrix.