Forum Discussion

slackerhx's avatar
slackerhx
Icon for Helper II rankHelper II
4 years ago
Solved

Dynamic calculations according to slicer value

Hi All, 

 

I'm getting a bit rusty with DAX calculations and will need help from the community. 

 

I'm trying to get the column of values (Workstations Required) recalculated based on the chosen value in Agility Ratio, where 

Workstations Required = HC * Agility Ratio

Area Required = Workstations Required * 10

 

 

 

I was able to have partial success by getting DAX to respond to the chosen Agility Ratio, however, there is no difference in Workstations Required each Year which should not be the case (please see above column with a hard coded Agility Ratio). Hence, there is something wrong with my DAX calculations and will require help. This was the DAX calculation that i used: 

 
CALCULATE(sum(Table1[HC]),ALLSELECTED(Table1[ID],Table1[Year]))*SELECTEDVALUE('Agility Ratio'[Agility Ratio])

 

Hope to hear from the community soon ! TIA!

Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi slackerhx ,

    According to your description, in the chart, the Year column is in the X-axis, so you shouldn't use "ALLSELECTED([Year])" in the formula, which will ignore current year in the X-axis and take all years into consideration.

    Modify the formula to:

    Measure =
CALCULATE ( SUM ( Table1[HC] ), ALLSELECTED ( Table1[ID] ) )
    * SELECTEDVALUE ( 'Agility Ratio'[Agility Ratio] )

    Get the correct result in my sample.

    Best Regards,
    Community Support Team _ kalyj

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

     

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi slackerhx ,

    Sorry maybe I'm not very clear about your description "there is no difference in Workstations Required each Year which should not be the case", do you mean that the Agility Ratio is different for each year? If this is the case, do you stil have a Agility Ratio slicer with a value selected?

    I create a sample to reproduce your problem.

    Table1:

    Agility Ratio table:

    The two tables have no relationship. With the measure you provided, can get the correct sum of HC by the Agility Ratio slicer.

    But I not sure what's your expected result, would you please explain more about it?

     

    Best Regards,
    Community Support Team _ kalyj

  • slackerhx's avatar
    slackerhx
    Icon for Helper II rankHelper II
    4 years ago

    Hi v-yanjiang-msft , 

     

    Yes, there is no relationship between Agility Ratio table and Table 1. 

     

    Based on my current DAX formula, each year will have the same result, despite a change in HC column each Year. Please see graph: 

     

    Incorrect

     

    The correct DAX formula should produce Workstations Required which varies according to the HC column in each year. 

    Correct

     

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi slackerhx ,

      According to your description, in the chart, the Year column is in the X-axis, so you shouldn't use "ALLSELECTED([Year])" in the formula, which will ignore current year in the X-axis and take all years into consideration.

      Modify the formula to:

      Measure =
CALCULATE ( SUM ( Table1[HC] ), ALLSELECTED ( Table1[ID] ) )
    * SELECTEDVALUE ( 'Agility Ratio'[Agility Ratio] )

      Get the correct result in my sample.

      Best Regards,
      Community Support Team _ kalyj

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