Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add specific rows

Hi Folks,   I would like to add specific columns as shown in the below attached picture. For UID = 1, Values2 For UID = 2, Values 2 For UID =3, Values1+Values2 For UID =4, Values2 For UID =5, ...
Need Help
Show and Tell
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Ah, I wasn't quite understanding where that number came from but I see now.

     

    Since it depends on a different row,  you'd need to do something a bit more like this:

    Calculated Column =
VAR _UID = TableA[UID]
VAR _Vals1 = TableA[values1]
VAR _Vals2 = TableA[values2]
VAR _Sum3 =
    SUMX (
        FILTER ( TableA, TableA[UID] = 3 ),
        TableA[values1] + TableA[values1]
    )
RETURN
    SWITCH (
        _UID,
        1, _Vals2,
        2, _Vals2,
        3, _Vals1 + _Vals2,
        4, _Vals2,
        5, _Sum3 + _Vals1 + _Vals2,
        _Vals2
    )

     

Tahreem24's avatar
Tahreem24
Icon for Super User rankSuper User
4 years ago

Anonymous Try this:

=SWITCH(True(),
MAX(UID)=1,SUM(values2),
MAX(UID)=2,SUM(values2),
MAX(UID)=3,SUM(values1)+SUM(values2),
MAX(UID)=4,SUM(values2),
MAX(UID)=5,2*(SUM(values1)+SUM(Values2)),
SUM(Values2))

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Could you provide a mock up please? Facing this,

     

     

    • Tahreem24's avatar
      Tahreem24
      Icon for Super User rankSuper User
      4 years ago

      Anonymous Properly follow the code and bracket as per my given DAX. I don't know why you close all MAX and SUM bracket's at the end. Follow my code step by step and open and close the bracket accordingly.

      Else, copy paste that DAX here instead of sharing screen shot.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Sorry, my bad. Below is the error-free DAX, but looks like it is printing the result of UID=5 to all the rows.

         

        Result = SWITCH(True(),
        MAX(TableA[UID])=1,SUM(TableA[values2]),
        MAX(TableA[UID])=2,SUM(TableA[Values2]),
        MAX(TableA[UID])=3,SUM(TableA[values1])+SUM(TableA[values2]),
        MAX(TableA[UID])=4,SUM(TableA[values2]),
        MAX(TableA[UID])=5,2*(SUM(TableA[values1])+SUM(TableA[Values2])),
        SUM(TableA[Values2]))

         

         

    • mh2587's avatar
      mh2587
      Icon for Super User rankSuper User
      4 years ago

      You have left the closing bracket ) in line number 4 

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    Variables make this a bit cleaner to read.

     

    Column =
VAR _UID   = MAX ( TableA[UID] )
VAR _Vals1 = SUM ( TableA[values1] )
VAR _Vals2 = SUM ( TableA[values2] )
RETURN
    SWITCH (
        _UID,
        1, _Vals2,
        2, _Vals2,
        3, _Vals1 + _Vals2,
        4, _Vals2,
        5, 2 * _Vals1 + _Vals2,
        _Vals2
    )

     

    If you like, you can also drop the lines for 1, 2, and 4 since they match the default value.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for giving me a clearer solution.  Easy to understand. But, as other solution, this one gives me the same sum in all the rows. Could you help please?

      Thanks.

      Result_1 =
      VAR _UID = MAX ( TableA[UID] )
      VAR _Vals1 = SUM ( TableA[values1] )
      VAR _Vals2 = SUM ( TableA[values2] )
      RETURN
      SWITCH (
      _UID,
      1, _Vals2,
      2, _Vals2,
      3, _Vals1 + _Vals2,
      4, _Vals2,
      5, 2 * _Vals1 + _Vals2,
      _Vals2
      )

       

       

      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User
        4 years ago

        Anonymous Try this code:

        Formula =
        VAR value1_ = CALCULATE(SUM(TableName[Value]),TableName[UID]=1)
        VAR value2_ = CALCULATE(SUM(TableName[Value]),TableName[UID]=2)
        VAR value3_ = CALCULATE(SUM(TableName[Value]),TableName[UID]=3)
        VAR value4_ = CALCULATE(SUM(TableName[Value]),TableName[UID]=4)
        VAR value5_ = CALCULATE(SUM(TableName[Value]),TableName[UID]=5)
        VAR value6_ = CALCULATE(SUM(TableName[Value]),TableName[UID]=6)
        RETURN SWITCH(MAX(TableName[UID]),
        1, value1_,
        2,value2_,
        3, value1_+value2_,
        4, value4_,
        5, value3_+value4_,
        6, value6_)