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, Result(UID3)+Values1+Values2

 

For all UIDs which do not have any Summation should display VALUES2 in the Result column.

 

 

TIA

 

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
    )

     

16 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The summation should be performed based on the UID formulas provided.

      Also, if it could be using measure, it would be more helpful.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Edited the Original post with Non-summation UID formulas. Also, Values1 and Values2 could be greater or lesser or equal. Could be null aswell.

  • Tahreem24's avatar
    Tahreem24
    Super 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
        Super 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.

         

         

    • AlexisOlson's avatar
      AlexisOlson
      Super 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
    Super User
    4 years ago

    Anonymous Try this Measure:

    Final Formula =
    VAR value1_ = CALCULATE(SUM(UIDTable[Values1]),UIDTable[UID]=1)
    VAR value2_ = CALCULATE(SUM(UIDTable[Values2]),UIDTable[UID]=2)
    VAR value3_ = CALCULATE(SUM(UIDTable[Values1])+SUM(UIDTable[Values2]),UIDTable[UID]=3)
    RETURN SWITCH(
    MAX(UIDTable[UID]),
    1, value1_,
    2,value2_,
    3,Sum(UIDTable[Values1])+SUM(UIDTable[Values2]),
    4, value2_,
    5,Sum(UIDTable[Values1])+SUM(UIDTable[Values2])+value3_
    )