What-if Parameter for Only Selected Values

[Anonymous]

Hey,

I'm trying to create visualizations for managing project resources and what-if scenarios. I have a table where I have the demand, a table where I have the project names, and a table where I have the resource names and their roles. 

I have succesfully created a parameter which allows me to adjust the demand for all projects/resources, but I would like to be able to choose only specific projects or resources and adjust their demand. 

So basically I have a matrix that would look like this:

Project Name             Demand

Project 1                      100

Project 2                      50

Project 3                      150

Total                            300

I would like to be able to choose only project 1 and adjust the demand by 10 % so the table would end up looking like this:

Project Name             Demand

Project 1                      110

Project 2                      50

Project 3                      150

Total                            310

But instead when I adjust the demand it always adjusts all the values by 10 % so that the total ends up to 330. 

My current Adjusted Demand uses this query:

Adjusted Demand = 

VAR

     AdjustedDemand = INTERSECT( VALUES( ProjectData[Project Name]), ALL(ProjectData[Project Name]))

RETURN

CALCULATE(

     SUMX(ResourceDemandData, (ResourceDemandData[Demand] * (1 + [What-if Demand Value]))), 

     AdjustedDemand)

Is there something wrong with the query and how should it be changed to only apply to the selected projects?

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

1.

Create a new table which has no relationship with other tables

slicer_project = SELECTCOLUMNS(VALUES(project[project]),"slicer_project",[project])

Add "slicer_project" in the slicer.

2.

Create what-if parameter

this is a measure from "parameter" table

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

3.

create measures in "project" table

Measure =
IF (
    SELECTEDVALUE ( slicer_project[slicer_project] ) = MAX ( project[project] ),
    MAX ( demand[demand] ) * ( 1 + [Parameter Value] ),
    MAX ( demand[demand] )
)

Measure 2 = IF(HASONEVALUE(project[project]),[Measure],SUMX(ALL(project),[Measure]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much for your answer, it worked 🙂 

To take things to the next level, can you help if it would it be possible to select multiple projects and change the demand for all of them? 

For example if I wanted to choose both projects 1 & 2 and change the demand by 10 % but leave project 3 the way it is.