Parameter Slicer that dynamically changes values on a chart

[PBAEIGuy]

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? 

[Anonymous]

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]

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?

[PBAEIGuy]

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.