Forum Discussion

PBAEIGuy's avatar
PBAEIGuy
Frequent Visitor
1 year ago

Parameter Slicer that dynamically changes values on a chart

I have a rough mock up of a chart on a dashboard with the following formula: 

Adjusted Revenue = SUMX(
    'Test Data',
    SWITCH(
        TRUE(),
        'Test Data'[Type] = "Type A",
        'Test Data'[Values] + (CALCULATE(SUM('Test Data'[Values]),'Test Data'[Type] = "Type C") * 'Shareholding Percentage'[Shareholding Percentage Value]),

        'Test Data'[Type] = "Type C",
        'Test Data'[Values] - ('Test Data'[Values] * 'Shareholding Percentage'[Shareholding Percentage Value]),

        'Test Data'[Values]
    )
)

I want my parameter slicer to simultaeniously reduce Type C and increase Type A depending on the shareholding percentage value, but it only ever seems to decrease Type C and not both A and C.

Can anyone advise? 
General Comment
Need Help
Show and Tell
Tips and Tricks

3 Replies

  • PBAEIGuy's avatar
    PBAEIGuy
    Frequent Visitor
    1 year ago

    The data I have used is a simple excel file that has 3 columns, Business Name, Type and Revenue. 

    I use the clustered column chart on PBI with Business Name on the X axis, Type on the legend and the above measure on the y axis. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi PBAEIGuy ,

    I create two tables as you mentioned.

    Then I think you  can create a Parameter.

    Next you can create a measure and here is the DAX code.

    Measure = 
SUMX(
    'Test Data',
    SWITCH(
        TRUE(),
        'Test Data'[Type] = "Type A",
        'Test Data'[Values] + (CALCULATE(SUM('Test Data'[Values]),'Test Data'[Type] = "Type C") * 'Parameter'[Parameter Value]),

        'Test Data'[Type] = "Type C",
        'Test Data'[Values] - ('Test Data'[Values] * 'Parameter'[Parameter Value]),

        'Test Data'[Values]
    )
)

     

     

    Best Regards

    Yilong Zhou

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

    • PBAEIGuy's avatar
      PBAEIGuy
      Frequent Visitor
      1 year ago

      I already tried that DAX, and it only seems to adjust the bars for the Type C as that reduces if the parameter value increases, but Type A doesn't increase with the value of the deduction from Type C. 

      Why is this?