Total accumulated value

[ThiagoTeles]

Olá. Preciso de uma medida no DAX que seja capaz de gerar os valores na coluna [TtAccV] (valor total acumulado). É uma planilha com um registro de negociações. [Ord]: Ordem de negociação, [Amt]: Quantidade negociada, [TtV]: Valor total da negociação. Se [Amt] < 0: VENDER. Se [Amt] > 0: COMPRE. Se COMPRAR, então [TtAccV] = [TtAccV] anterior + [TtV]. Se VENDER, então [TtAccV] = [Amt] acumulou * ([TtAccV] da última VENDA + [TtV] acumulado entre a última VENDA e a VENDA atual). Abaixo está uma tabela do Excel como ela deve parecer no PowerBI.

Ord	Amt	TtV	TtAccV
1	2	4	4,00
2	3	9	13,00
3	5	10	23,00
4	-2		18,40
5	4	12	30,40
6	5	15	45,40
7	8	24	69,40
8	-5		55,52
9	3	6	61,52
10	7	14	75,52
11	9	27	102,52
12	-8		81,49
13	3	6	87,49
14	2	5	98,49
15	4	7	116,49

[amitchandak]

@ThiagoTeles , Better to have a separate Order table with distinct Ord, then try a measure like

TtAccV =
VAR _current = MAX('Orders'[Ord])
VAR _max =
CALCULATE(
MAX('Trades'[Ord]),
FILTER(
ALL('Orders'),
'Orders'[Ord] < CurrentOrder ), 'Trades'[Amt] < 0
)
)
VAR _last =
CALCULATE(
MAX('Trades'[TtAccV]),
FILTER(
ALL('Trades'),
'Trades'[Ord] = _max
)
)
VAR _sells =
CALCULATE(
SUM('Trades'[TtV]),
FILTER(
ALL('Trades'),
'Trades'[Ord] > _max && 'Trades'[Ord] <= CurrentOrder) , 'Trades'[Amt] > 0
)
)
RETURN
IF(
sum('Trades'[Amt]) > 0,
SUMX(
FILTER(ALL('Trades'), 'Trades'[Ord] <= _current),
'Trades'[TtV]
),
sum('Trades'[Amt]) * (_last + _sells)
)

[ThiagoTeles]

Thanks so much for awenser the question. I'm try your soluction, but failure. You created a circular reference in MAX('Trades'[TtAccV]) while you are referring to the measure you want to create [TtAccV].
I need the measurement itself [TtAccV] to be the column in the table visual.
See the solution in Excel below (Note that the big challenge is to make TtAccV work from the first SALE onwards):

[v-jianpeng-msft]

Thank you amitchandak 

Hi, @ThiagoTeles 

As you mentioned, the hard part is working from the first SALE. In the screenshot you provide, the value that follows you relies on the TtAccV calculated from the first SALE. The TtAccV corresponding to the first SALE depends on the previous TtAccV. And in this particular place, the algorithm is not the same as the previous one. This makes it difficult to implement DAX in Power BI.
Calculations for measures in Power BI don't do calculations based on the current cells like Excel does. The need to think about starting with the first SALE, which in Power BI is often referred to as iterations. Iteration is not supported in DAX. You can learn about this fact from the blog below.

Previous Value (“Recursion”) in DAX - Microsoft Fabric Community

If you insist on using DAX to implement it, you're running into circular dependencies, and you're only going to be able to get close to it, and the code in DAX is very redundant.
Instead, you can calculate it in Excel and then import it into Power BI. Calculating your columns via Excel is an effective way to solve the recursion problem. 

I used the sample data you provided:

To properly calculate 18.4, I set up a measure using the following dax expression:

Measure = 
VAR _firstSale = MINX(SUMMARIZE(FILTER(ALL('Table'),'Table'[Amt]<0),'Table'[Ord]),'Table'[Ord])
VAR _table = SUMMARIZE(FILTER('Table','Table'[Ord]<=_firstSale),'Table'[Ord],'Table'[Amt],'Table'[TtV],"TTAccv",
    VAR _current_id = 'Table'[Ord]
    VAR _totalTtV = CALCULATE(SUM('Table'[TtV]),FILTER(ALL('Table'),'Table'[Ord]<=_current_id))
    VAR _total_Amt = CALCULATE(SUM('Table'[Amt]),FILTER(ALL('Table'),'Table'[Ord]<=_current_id))
    VAR _total_Amt_1 = CALCULATE(SUM('Table'[Amt]),FILTER(ALL('Table'),'Table'[Ord]<_current_id))
    RETURN IF('Table'[Amt]>0,_totalTtV,_total_Amt*_totalTtV/_total_Amt_1)
)
VAR _curren_id = SELECTEDVALUE('Table'[Ord])
RETURN IF(MAXX(FILTER(_table,'Table'[Ord]=_curren_id),[TTAccv])<>BLANK(),MAXX(FILTER(_table,'Table'[Ord]=_curren_id),[TTAccv]))

In this case, the Ord=5 calculation relies on the value of 18.4, so I created a new metric using the following DAX expression

Measure 2 = 
VAR _firstSale = MINX(SUMMARIZE(FILTER(ALL('Table'),'Table'[Amt]<0),'Table'[Ord]),'Table'[Ord])
VAR _id = SELECTEDVALUE('Table'[Ord])-_firstSale
VAR _value = MINX(FILTER(ALL('Table'),'Table'[Ord]=_firstSale),[Measure])
VAR _id2 = IF(_id=1,
    SELECTEDVALUE('Table'[Ord])
)
VAR _id3 = SELECTEDVALUE('Table'[Ord])
VAR _table = SUMMARIZE(FILTER(ALL('Table'),'Table'[Ord]>=_firstSale+1),'Table'[Ord],'Table'[Amt],'Table'[TtV])
VAR _total_Ttv = SUMX(FILTER(_table,'Table'[Ord]<=_id3),'Table'[TtV])
VAR _total_Amt = CALCULATE(SUM('Table'[Amt]),FILTER(ALL('Table'),'Table'[Ord]<=_id3))
VAR _total_Amt_1= CALCULATE(SUM('Table'[Amt]),FILTER(ALL('Table'),'Table'[Ord]<_id3))
RETURN IF(_id<=0,[Measure],IF(_id3>=_id2,IF(SELECTEDVALUE('Table'[Amt])>0,_total_Ttv+_value,_total_Amt*(_total_Ttv+_value)/_total_Amt_1)))

When Ord=9, we need to rely on the previous value of 55.52.
This is only a simple example of the data you provided. If you have very large data, then a very large number of measures, this is unrealistic and difficult to maintain.

Solving the problem in Excel is much easier than using DAX. I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.