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
    )

     

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_)
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

      Tahreem24 , I think you have got confused with one of my other post request. This post request is the summation across columns as well. 

       

      The other post if only within the same columns but multiple selected rows.

       

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