PowerBI calculated measure - DAX

[Longdisplay]

Hi Everyone, I have created a measure which calculates KPI based on selection in Slicer; when the particular product "A" or "B" or "C" is selected then the measure value returns, but when I have two different values selected in slicer like (A and B) - my calculated measure returns an error. My Slicer has distinct values like A,B,C to make selection. Please suggest - Below is my calculated measure :

TARGET =
IF (
    ISCROSSFILTERED ( Product[Product_Type] ),
    SWITCH (
        TRUE (),
        VALUES ( Product[Product_Type] ) = "A",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] <> "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            )
                + (
                    (
                        CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] = "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                    ) * ( 0.43 )
                ),
        VALUES ( Product[Product_Type] ) = "B",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.07
            ),
        VALUES ( Product[Product_Type] ) = "C",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            ),
        BLANK ()
    ),
    BLANK ()
)

@Greg_Deckler,@v-jiascu-msft @Anonymous @selimovd @Fowmy @amitchandak @parry2k @Jihwan_Kim 

[parry2k]

@Jihwan_Kim But that is not a true fix, it will not work if user selects more than one value, better to have multiple values selected, and show sum as @Longdisplay illustrated in his screenshot. Just my 2 cents. 

[Jihwan_Kim]

In my opinion, instead of using VALUES, try using SELECTEDVALUES.

In that case, I think, if more than two products are selected in the slicer, it will return a blank.

[parry2k · Accepted solution]

@Longdisplay I will revisit this measure and maybe rewrite it this way. Again, please double-check the results.

TARGET =
IF (
    ISCROSSFILTERED ( Product[Product_Type] ),
    SUMX ( VALUES ( Product[Product_Type] ) ),
    
    SWITCH (
        Product[Product_Type],
        "A",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] <> "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            )
                + (
                    (
                        CALCULATE ( SUM ( RM[Dollar] ), RM[STATE] = "TX", YEAR ( RM[Date] ) = 2020 ) / 12
                    ) * ( 0.43 )
                ),
       "B",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.07
            ),
       "C",
            (
                (
                    CALCULATE ( SUM ( RM[Dollar] ), YEAR ( RM[Date] ) = 2020 ) / 12
                ) * 1.10
            ),
        BLANK ()
    ) --switch closing
    ), --sumx closing
    BLANK ()
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[selimovd]

Hey @Longdisplay ,

you get the error because you are checking with multiple selections a list returned by VALUES to a string, for example "A".

As long as there is only one value that works as it's transformed to a single row, but when there are more values you will get an error.

First I would change the VALUES ( Product[Product_Type] ) to MAX( Product[Product_Type] ). This should fix the error.

If you really want to select only one value, I would already change the slicer to single selection:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Longdisplay]

Hi @selimovd ; I am getting inaccurate total from calculated measure when I have Product A and Product B Selected together - for example 

Total (from measure)	143,661
Product A	129,642
Product B	17,555
Accurate Total should be	147,197

Can you please suggest?