Scenarios in Projection Calculation

DAX
1

Hi there

I am stuck and would really appreciate some assistance with the use of scenarios within DAX.

I need to allow users to add 3 scenarios to a projections calculation within a PBI report. I am having troubles with mapping the selected Program and SPR to existing Program and SPR. I have tried modifying the [Projected Salaries Scenario] and [Projected Salaries CalcScenarios] measures with no success.

The current method I followed was to map selected values to ScenarioID within the fact table, which has a unique ID. I am open to alternative methods to achieve the desired output should it be easier, including an alternative Projection calculation (although not a priority as the current non-scenario measure works as intended), but ultimately require context to the selected Program and SPR added within the Projection Scenario calculations which should then allow for mapping to existing Program and SPR values.

With the following scenarios:

image
image975×218 40.1 KB

This is the desired output:

image
image759×405 91.1 KB

I have attached PBI report and corresponding data.
Test Projections.pbix (449.8 KB)
Projections Data.xlsx (1013.3 KB)

Please let me know if any questions.

Thanks
Peter

1 Like
2

Bumping this post for more visibility from our experts and users.

1 Like
3

Hi @PeterF - With current calculation, I can see issue with below.

VAR DailyRate = 1.25*(7.5*SELECTEDVALUE(MGM_HRData_StaffingProfileGap[Rate]))

For Project ID 150 and SPR1 and SPR8, there are multiple Rates in MGM_HRData_StaffingProfileGap and hence Selectedvalue(Rate) is blank and inturn DailyRate is also blank. Please fix it and check.

Thanks
Ankit J

4

Hello @PeterF

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

5

Thanks @ankit !
It is correct to reference that in the current structure I have set up - the [Projection Salaries Scenario] brings context into the SelectedValue(Rate) via the ID of the MGM_HRData_StaffingProfileGap hence SelectedValue(Rate) is not blank:

Projected Salaries Scenario =
    SUMX (
        values ( MGM_HRData_StaffingProfileGap[ID] ),
       [Projected Salaries CalcScenarios]
    )

The issue I am facing is how to integrate the selected value (program) from the Scenario 1 Program ID, Scenario 2 Program ID slicers (scenarios). Is it even possible to have the Selected Value (program) treated as MGM_Program[Program] with MGM_HRData_StaffingProfileGap[Program_BID] as blank on the non-blank ScenarioID rows and there is a one to many relationship with MGM_HRData_StaffingProfileGap and MGM_Program table? I concluded the below attempt was unsuccessful due to this. I tried integrating the [Scenario Program ID] measure into the PWContextAmount Variable within ScenarioCalc:

VAR ScenarioProgramTable =
SWITCH(
SelectedScenario,
1, SELECTEDVALUE(‘Scenario 1 Program ID’[Program_BID]),
2, SELECTEDVALUE(‘Scenario 2 Program ID’[Program_BID]),
3, SELECTEDVALUE(‘Scenario 3 Program ID’[Program_BID]),
BLANK()
)

VAR PWContextAmount =
SWITCH(
SelectedScenario,
1,
CALCULATE(
SWITCH(
TRUE(),
WorkingDays <= WorkingDaysinPW,
DailyRate * WorkingDays,
WorkingDays > WorkingDaysinPW,
(DailyRate * 10) * [Scenario FTE]
),
FILTER(
MGM_Program,
MGM_Program[Program_BID] = ScenarioProgramTable
&& MGM_Program[Program_BID] IN VALUES(MGM_HRData_StaffingProfileGap[Program_BID])
)
),
2,
CALCULATE(
SWITCH(
TRUE(),
WorkingDays <= WorkingDaysinPW,
DailyRate * WorkingDays,
WorkingDays > WorkingDaysinPW,
(DailyRate * 10) * [Scenario FTE]
),
FILTER(
MGM_Program,
MGM_Program[Program_BID] = ScenarioProgramTable
&& MGM_Program[Program_BID] IN VALUES(MGM_HRData_StaffingProfileGap[Program_BID])
)
),
3,
CALCULATE(
SWITCH(
TRUE(),
WorkingDays <= WorkingDaysinPW,
DailyRate * WorkingDays,
WorkingDays > WorkingDaysinPW,
(DailyRate * 10) * [Scenario FTE]
),
FILTER(
MGM_Program,
MGM_Program[Program_BID] = ScenarioProgramTable
&& MGM_Program[Program_BID] IN VALUES(MGM_HRData_StaffingProfileGap[Program_BID])
)
),
BLANK()
)

Any ideas of how to integrate selected Program?

Thanks
P

6

Bumping this post for more visibility from our experts and users.

7

Hi @PeterF

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts’ and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!