Add specific rows

[Anonymous]

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

[Tahreem24]

@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_

)

[Tahreem24]

@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))

[AlexisOlson]

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]

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]

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]

@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 · Accepted solution]

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]

@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]

@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.

[Anonymous]

Could you provide a mock up please? Facing this,

[mh2587]

You have left the closing bracket ) in line number 4 

[Tahreem24]

@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]

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]))

[Anonymous]

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

[mh2587]

column = IF(value2>value1,Value1+value2,Value2)

[Anonymous]

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

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