Add cell value of multiple rows in same column

[Anonymous]

Hello,

Can someone throw some light on the implementation of the below scenario.

UID	Value	Expected Result	Formula for Math
1	100	100	=Value(1)
2	20	20	=Value(2)
3		120	=Value(1)+Value(2)
4	200	200	=Value(4)
5		320	=Value(3)+Value(4)
6	750	750	=Value(6)

Thanks in advance. 

[Tahreem24]

@Anonymous  Try this measure:

Formula =

VAR value1_ = CALCULATE(SUM(UIDTable[Value]),UIDTable[UID]=1)

VAR value2_ = CALCULATE(SUM(UIDTable[Value]),UIDTable[UID]=2)

VAR value3_ = CALCULATE(SUM(UIDTable[Value]),UIDTable[UID]=3)

VAR value4_ = CALCULATE(SUM(UIDTable[Value]),UIDTable[UID]=4)

VAR value5_ = CALCULATE(SUM(UIDTable[Value]),UIDTable[UID]=5)

VAR value6_ = CALCULATE(SUM(UIDTable[Value]),UIDTable[UID]=6)

RETURN SWITCH(MAX(UIDTable[UID]),

1, value1_,

2,value2_,

3, value1_+value2_,

4, value4_,

5, value3_+value4_,

6, value6_)

[Anonymous]

Sorry to trouble you. Am I doing anything wrong here? I am unable to get the same value as you get.

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

[Tahreem24]

@Anonymous  Create the measure not column.

[Anonymous]

If its a measure, I get the same values. 

[Tahreem24]

@Anonymous , what is the data type of UID? is it a numeric value or text ?

[Anonymous]

@Tahreem24 , 

UID is Whole Number 

Value is Whole Number.

[Tahreem24]

@Anonymous But i am not getting any issue that;s why I attached with screen shot.

[Anonymous]

@Tahreem24 , This is strange. Can you share the PBIX ?

[Tahreem24]

@Anonymous I am attaching PBIX file for your reference.

[Anonymous]

@Tahreem24 , Thanks for the report. Understood what went wrong. I was having VALUE column in 'Dont Summarize', that should be in Summarize.

Besides, for UID=5, it should be Resultant(UID3)+UID4

[jaideepnema]

Hi @Anonymous ,

Create a calculated column like this

Expected Value = IF(ISBLANK(Data[Value]),CALCULATE(SUM(Data[Value]),Data[ID]<=EARLIER(Data[ID]),ALL(Data)),Data[Value])

Where data is the table name of your dataset being used

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀

[Anonymous]

Thanks for your efforts. 

I might have missed this. But, the summation is not always with EARLIER cell value or <= case.

It could beRow 3= Row1+Row2,

Row 9 = Row 2+Row7,

Row 10 = Row7+ Row9