Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
D_PBI
Post Patron
Post Patron

How to sum values based on user slicer selection combination?

Hi,
I have created several Power BI Parameters that are set in the Power BI Service. These parameters are fed, via PQ, to the report. These parameters (these values) are used as a target value.
The team slicer visual consists of three teams (LS, PG, and AHSS). Each team has a parameter value.
I would like it so if the user selects all three teams (whether individually all, or through a 'select all') in the slicer then all three team's parameter values are summed.
If only, say, LS and PS teams are select then only the LS and PS parameters values are summed.
If only, say, LS teams is selected then only the LS parameter value is shown.

Below is my attempt at geting this to work.

__Deal target =

VAR _selectedvalues = VALUES( '__dim_Sub-Team'[Sub-Team] )

 

VAR _result =

IF(

    CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000000" && "200000001" && "200000002" ),

    VALUES( 'LS Deal target'[LS Deal target] ) + VALUES( 'PS Deal target'[PS Deal target] ) + VALUES( 'AHSS Deal target'[AHSS Deal target] ),

    IF(

        CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000000" && "200000001" ),

        VALUES( 'LS Deal target'[LS Deal target] ) + VALUES( 'PS Deal target'[PS Deal target] ),

        IF(

            CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000000" &&  "200000002" ),

            VALUES( 'LS Deal target'[LS Deal target] ) + VALUES( 'AHSS Deal target'[AHSS Deal target] ),

            IF(

                CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000001" && "200000002" ),

                VALUES( 'PS Deal target'[PS Deal target] ) + VALUES( 'AHSS Deal target'[AHSS Deal target] ),

                IF(

                    CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000000" ), //"LS"

                    VALUES( 'LS Deal target'[LS Deal target] ),

                    IF(

                        CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000001" ), //"PS"

                        VALUES( 'PS Deal target'[PS Deal target] ),

                        IF(

                            CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team],  "200000002" ), //"AHSS"

                            VALUES( 'AHSS Deal target'[AHSS Deal target] ),

                            100

                        )

                     )

               

                )

            )

        )

    )

)

 

RETURN

    _result + 0


What I get returned is the following error:

D_PBI_0-1716222994781.png


Please can someone help with this error?
Thanks.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@D_PBI , I think the issue is because of the values , Try like

Deal Target =
VAR _selectedValues = VALUES('__dim_Sub-Team'[Sub-Team])
RETURN
SWITCH(
TRUE(),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUMX(VALUES('LS Deal target'[LS Deal target]), [LS Deal target]) +
SUMX(VALUES('PS Deal target'[PS Deal target]), [PS Deal target]) +
SUMX(VALUES('AHSS Deal target'[AHSS Deal target]), [AHSS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001"),
SUMX(VALUES('LS Deal target'[LS Deal target]), [LS Deal target]) +
SUMX(VALUES('PS Deal target'[PS Deal target]), [PS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUMX(VALUES('LS Deal target'[LS Deal target]), [LS Deal target]) +
SUMX(VALUES('AHSS Deal target'[AHSS Deal target]), [AHSS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUMX(VALUES('PS Deal target'[PS Deal target]), [PS Deal target]) +
SUMX(VALUES('AHSS Deal target'[AHSS Deal target]), [AHSS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000"),
SUM('LS Deal target'[LS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001"),
SUM('PS Deal target'[PS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUM('AHSS Deal target'[AHSS Deal target]),
100 // Default or other cases
)

View solution in original post

3 REPLIES 3
v-xuxinyi-msft
Community Support
Community Support

Hi @D_PBI 

 

According to your error screenshot, the parameter types in your CONTAINS function have text type and true/false type, which cannot be compared, for example, "CONTAINS( _selectedvalues, '__dim_Sub-Team'[Sub-Team], "200000000" && " 200000001" && "200000002" )", where the first two arguments are text types and the latter is a true/false type. My current suggestion is that you need to change one of the parameter types in order for the comparison to work.

 

If your problem persists, please provide the sample data and the expected results based on the sample data so that I can better help you. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show it as screenshots. Please remove any sensitive data in advance. 

 

Best Regards,
Yulia Xu

 

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

amitchandak
Super User
Super User

@D_PBI , I think the issue is because of the values , Try like

Deal Target =
VAR _selectedValues = VALUES('__dim_Sub-Team'[Sub-Team])
RETURN
SWITCH(
TRUE(),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUMX(VALUES('LS Deal target'[LS Deal target]), [LS Deal target]) +
SUMX(VALUES('PS Deal target'[PS Deal target]), [PS Deal target]) +
SUMX(VALUES('AHSS Deal target'[AHSS Deal target]), [AHSS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001"),
SUMX(VALUES('LS Deal target'[LS Deal target]), [LS Deal target]) +
SUMX(VALUES('PS Deal target'[PS Deal target]), [PS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUMX(VALUES('LS Deal target'[LS Deal target]), [LS Deal target]) +
SUMX(VALUES('AHSS Deal target'[AHSS Deal target]), [AHSS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001") && CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUMX(VALUES('PS Deal target'[PS Deal target]), [PS Deal target]) +
SUMX(VALUES('AHSS Deal target'[AHSS Deal target]), [AHSS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000000"),
SUM('LS Deal target'[LS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000001"),
SUM('PS Deal target'[PS Deal target]),
CONTAINS(_selectedValues, '__dim_Sub-Team'[Sub-Team], "200000002"),
SUM('AHSS Deal target'[AHSS Deal target]),
100 // Default or other cases
)

@amitchandak - thank you. That worked as needed.
@v-xuxinyi-msft - thanks for your response. I have followed @amitchandak method to resolve the need.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.