Forum Discussion

smithbj's avatar
smithbj
New Member
2 years ago
Solved

Using 'What-if" parameter to modify a value in a column but getting the summarized value

I'm creating a dashboard to show employee utilization. I used a simple DAX formula 

Utilization_Shop = [Duration] / [Capacity] 

// [Duration] = total accumulated time employees were in a project.
// [Capacity] = Total availible time assigned employees could have spent the project. This a calculated column created with the following DAX:  
 Capacity = [Compliment] * 420
 
I want to see how utilization would be effected by adding more people to the shop which would add to the [Compliment] value. To do this, I created a "What if" slicer that resulted in the following measure: 
Adjusted Compliment Value = SELECTEDVALUE('Adjusted Compliment'[Adjusted Compliment], 0)
 
I then created a new measure to add the original compliment and the adjusted value together:
Adjusted Shop  Compliment = SUM('Trouble Call History'[Compliment]) + 'Adjusted Compliment'[Adjusted Compliment Value]
 
(My Problem)
The DAX does add the [Adjusted Compliment Value], however it adds it to the summarized total for the [Compliment]. For instance, if a particular shop has a compliment of 6 and they work on 25 projects, the summerized capacity that is calulated is 150 (6x25). How do I get the new measure to calulate the [Adjusted Compliment Value] to the unsummarized value of [Compliment]? 
 
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi smithbj 

     

    It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

     

    Best Regards    

    Zhengdong Xu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    smithbj , Not very clear

    Try like

     

    Adjusted Shop  Compliment = SUMX('Trouble Call History', 'Trouble Call History'[Compliment] + 'Adjusted Compliment'[Adjusted Compliment Value] )

     

    Or

     

    Adjusted Capacity =
    SUMX(
    'Trouble Call History',
    ([Compliment] + [Adjusted Compliment Value]) * 420
    )

    • smithbj's avatar
      smithbj
      New Member
      2 years ago

      Thank you for the quick reply. I tried the first suggestion already but will try the 2nd suggestion when I get back to my computer and update the thread.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi smithbj 

         

        It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

         

        Best Regards    

        Zhengdong Xu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.