Multiple measure value into a column using SWITCH return incorrect value

[audrey0210]

Hello Everyone,

I'm new to Power BI. I want to put multiple measure values into a single column. I use the switch function to achieve that. But somehow the spending data return incorrect values as I expect.

Below is the correct data in the card.

Here are the incorrect data in the table. Only the 2022 Spend data is incorrect, and the other two goals have the correct data.

Here is the method I used to put multiple measures into a single column.

--------------------------

WebCIS SBA Measure =

VAR _CategoryNAME = { "Large","HUB"}

VAR _ClassVALUE =

    ADDCOLUMNS(

        _CategoryNAME,

            "2022 Goal",

            SWITCH(

                [Value],

                "HUB", [HUB Goal_M]

            ),

          "2022 Goal%",

            SWITCH(

                [Value],

                "HUB", [HUB Goal%_M]

            ),

              "2022 Spend",

            SWITCH(

                [Value],

                "Large",[2022 Large Spend],

                "HUB", [2022 HUB_SBA Spend]

            )

    )

RETURN

    _ClassVALUE
--------------------------------

Here are the measures' queries.

2022 HUB_SBA Spend = CALCULATE(SUM('WebCIS SBA 10 21-09 22'[Invoice Value in company code currency]),FILTER('Diversity Class','Diversity Class'[IS HUB] = "Y"))

2022 Large Spend = CALCULATE(SUM('WebCIS SBA 10 21-09 22'[Invoice Value in company code currency]), FILTER('WebCIS SBA 10 21-09 22','WebCIS SBA 10 21-09 22'[PO] = "with" && 'WebCIS SBA 10 21-09 22'[Exception] = BLANK()&&'WebCIS SBA 10 21-09 22'[Is SMALL] = BLANK()))

HUB Goal_M = CALCULATE(SUM('WebCIS SBA Goal'[HUB Goal]))

HUB Goal%_M = CALCULATE(SUM('WebCIS SBA Goal'[HUB Goal%]))

--------------------------
I don't know why the first two columns have the correct data; only the last one has the incorrect data.

Can someone help me with this problem?

Thank you very much!!

[audrey0210]

I see. Thank you for the explanation.

[lbendlin]

That's not what SWITCH is for.  Its purpose is to probe a series of assumptions and then exit once one of them evaluates to true.    For simple yes/no tests use IF().

Avoid using SWITCH inside the ADDCOLUMNS.  Move it outside.