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
    )

     

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
)

 

 

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You must have defined it as a calculated column. It works fine as a measure.

 

For a calculated column, remove the MAX/SUM/SUM aggregations from the variable definitions.

Calculated Column = 
VAR _UID   = TableA[UID]
VAR _Vals1 = TableA[values1]
VAR _Vals2 = TableA[values2]
RETURN
    SWITCH (
        _UID,
        1, _Vals2,
        2, _Vals2,
        3, _Vals1 + _Vals2,
        4, _Vals2,
        5, 2 * _Vals1 + _Vals2,
        _Vals2
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    AlexisOlson , The result for UID=5 doesn't match with the Expected Result. I see you have multiplied with 2. But for UID5, it is the SUM(Value1(UID=5)+Value2(UID=5) + CalculatedSum(UID=3))

     

    So it would be 1000 + 1500 + 200 = 2700.

    Here 200 is the Calculated sum for UID=3.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      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
    )