Switching Calculation with Field Parameters in Power BI Visuals

[Ronald123]

Hey Power BI community,

I've noticed that since 2022, there's an option in Power BI to switch between calculations using field parameters. However, I'm currently working on a visual where I want to sum up the relevant calculation after selecting a parameter. Is this possible, or is there a workaround for this scenario?

Greets,

Ronald

[Ronald123]

@MFelix,

Thank you for your response.

When I use the formula now (the 1st option), there is no total when I select 2 parameters at the same time. Can you help me modify the formula so that it adds Calculation A and Calculation B together?

Best regards,

Ronald

[MFelix]

Hi @Ronald123 ,

To what I can understand you want to be abble to make the sum of the selected measure based on the field parameter? 

So for example if you selecte Measure A and the values are 1 , 2, 3 you get 6 if you selecte measure B and the values are 4, 5, 6 you get 15. Is this correct?

In this case you i believe you have two options one is two create a measure that uses a switch function changing the context of your calculation it would be something similar to this:

Total Sum = SUMX (
    ADDCOLUMNS (
        VALUES ( Table[Column] ),
        "ValuesTotal",
            SWITCH (
                SELECTEDVALUE ( Parameter[Parameter Order] ),
                0, [Measure A],
                1, [Measure B],
                2, [Measure C]
            )
    ),
    [ValuesTotal]
)

Another option that you have is to have a calculation group created using tabular editor that way the measure would be different:

SUMX (
    ADDCOLUMNS (
        VALUES ( Table[Column] ),
        "ValuesTotal", SELECTEDMEASURE ()
    ),
    [ValuesTotal]
)

Check this link for the Calculation groups option:

https://www.sqlbi.com/articles/introducing-calculation-groups/

https://www.sqlbi.com/calculation-groups/

The combination of calculation items with field parameters is very powerfull.

[Ronald123]

Hello Miguel Félix,

Thank you for your response.

When I use the formula now (the 1st option), there is no total when I select 2 parameters at the same time. Can you help me modify the formula so that it adds Calculation A and Calculation B together?

Best regards,

Ronald

[Ronald123]

Hello @MFelix ,

To complement my question, I am hereby sending you my test file.

Greets,

Ronald

https://drive.google.com/file/d/12-g6iJyUI2NtOzUPZ2Pwv0sKM3Mhhb8f/view?usp=drive_link

[MFelix]

Hi @Ronald123 ,

the google link is asking for a password.

[Ronald123]

Hi @MFelix ,

The test file can now be accessed without a password.

Greets,

Ronald

[MFelix · Accepted solution]

Hi @Ronald123 ,

Redo the measure to:

Total Sum = 
VAR _a =
    SELECTCOLUMNS ( Parameter, "Name",[Parameter])
RETURN
    SUMX (
        _a,
        SWITCH (
            [Name],
            "APPEL", [APPEL],
            "ORANGE", [ORANGE],
            "BANANA", [BANANA],
            0
        )
    )

I added a new row Banana to get some different results but it's working has needed: