Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
2 years ago
Solved

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:


Please can someone help with this error?
Thanks.

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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
    )

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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
    )

    • D_PBI's avatar
      D_PBI
      Icon for Post Partisan rankPost Partisan
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.