Forum Discussion

alexjaco's avatar
alexjaco
Frequent Visitor
5 years ago
Solved

Applying a percent Increase to a only values select by a parameter

Hi, 

 

I am looking for a formula to apply a percent increase to my [Cycle Time (Hrs)*] based on the task selected by a parameter and return all other [Cycle Time (Hrs)*] without the percent increase.  The parameter is [Selected Task] and the 'Percent Increase to Cycle Time'[Percent Increase to Cycle Time Value] is a percentage parameter as well. I have the following formula, but it doesn't work. 

 

IF([Selected Task]=SELECTEDVALUE(Sheet1[Task]), SUMX(Sheet1,SUM(Sheet1[Cycle Time (Hrs)*])*(1+'Percent Increase to Cycle Time'[Percent Increase to Cycle Time Value])), SUMX(Sheet1,SUM(Sheet1[Cycle Time (Hrs)*])))
 
 
DAX
parameters
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    Ah. I think the total may be correct but for the individual rows, we need to keep the filter context from the visual so that not all rows are the same value.

     

    Try this:

    Adjusting 1 Cycle Time (hrs) =
VAR SelectedTasks = VALUES ( Task[Task] )
RETURN
    CALCULATE (
        SUM ( Sheet1[Cycle Time (Hrs)*] ),
        KEEPFILTERS ( Sheet1[Task] IN SelectedTasks )
    ) * ( 1 + [Percent Increase to Cycle Time Value] )
        + CALCULATE (
            SUM ( Sheet1[Cycle Time (Hrs)*] ),
            KEEPFILTERS ( NOT ( Sheet1[Task] IN SelectedTasks ) )
        )

     

7 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    5 years ago

    You'll need a separate table for your task parameter.

     

    This version should be more efficient than SUMX:

    VAR SelectedTasks = VALUES ( ParamTask[Task] )
RETURN
    CALCULATE (
        SUM ( Sheet1[Cycle Time (Hrs)*] ),
        Sheet1[Task] IN SelectedTasks
    ) * ( 1 + 'Percent Increase'[Percent Increase] )
        + CALCULATE (
            SUM ( Sheet1[Cycle Time (Hrs)*] ),
            NOT ( Sheet1[Task] IN SelectedTasks )
        )

     

    • alexjaco's avatar
      alexjaco
      Frequent Visitor
      5 years ago

      Thank you for the solution. I applied your formula, however it's now returning the selected ParamTask[Task] 

      [Cycle Time (Hrs)*] to every task and applying the percent increase. So for example if Task A was selected then task B and C would now reflect Task A's [Cycle Time (Hrs)*] (with the percent increase applied).  
      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        5 years ago

        Hmm. Does your task parameter table have a relationship with Sheet1? (Parameter tables should be unrelated.)