I need a dax calculate average cost

[Efren2000]

Hi all

I need a dax that calculate average cost, I have the following columns where my column index is "UltimaCambio" and This is what it looks like:

I calculated last row with following formula beacuse the average cost is calculated:

Average Cost = Total Cost / Number of Units Produced

and my total cost is calculated multiplicate my last accumulated cost * accumulated cant

and I use the following dax for calculate the last row:

_Costo Unitario Calculado =

VAR fechamin = MAX(Movimientos[UltimoCambio])

VAR fecha_anterior = CALCULATE(MAX(Movimientos[UltimoCambio]),TOPN(1,FILTER(ALLSELECTED(Movimientos),Movimientos[UltimoCambio]< fechamin)))

RETURN fecha_anterior

I have this excel with the columns calculated and this is example how it has to be seen:

[Anonymous]

Hi @Efren2000 

If I understand you correctly, you want to calculate the average cost. Do you need to calculate the average of the column Costo x Tipo de Cambio? So in your example data, the average is 46.65, is that correct? 

Since you gave two screenshots, but some of the column names seem to be different in both screenshots, I'm a bit confused and need to check with you. Is the second screenshot your expected result?

Do Cantidad Mov., COSTO CALCULADO and Acumulado Cantidad in Figure 2 correspond to Movimientos, Costo x Tipo de Cambio and Saldo Final in Figure 1 respectively?

It would be great if you could explain your needs in more detail. Thanks.

Best Regards,
Yulia Xu

[Efren2000]

Thak you for respond

I need a dax where it is calculated Average Cost = Total Cost (Total Valor Inv) / Number of Units Produced (Accumulated Amount)

Row A4: In PBI, we already calculte this values from previous month

Row A5: Actual Value =E5*L4

              Accumulated Amount: We already have the measure

             Acumulated Value: =H4*L4

             Total Valor Inv =G5+I5

             Cost calculate= J5/H5

Row A6: Actual Value =E6*L5

              Accumulated Amount: We already have the measure

             Acumulated Value: =H5*L5

             Total Valor Inv =G6+I6

             Cost calculate= J6/H6

Row A10: Actual Value =E10*F10

              Accumulated Amount: We already have the measure

             Acumulated Value: =H9*L9
             Total Valor Inv =G10+I10
             Cost calculate= J10/H10

Row A11 Same as Row A6…

This is what I have in PBI 

In this dax I recover the last value:

_Fecha Anterior =

VAR fechamin = MAX(Movimientos[UltimoCambio])

VAR fecha_anterior = CALCULATE(MAX(Movimientos[UltimoCambio]),FILTER(ALLSELECTED(Movimientos),Movimientos[UltimoCambio]<fechamin))

RETURN fecha_anterior

Acumulado Cantidad =

[_Ending Balance date] + [Saldo Almacen Mes Anterior Detalle]

Saldo Almacen Mes Anterior Detalle =

VAR _index = SELECTEDVALUE(Movimientos[UltimoCambio])

VAR _artical = SELECTEDVALUE(Movimientos[Articulo])

VAR _mov = SELECTEDVALUE(Movimientos[Mov])

var totalant= CALCULATE([SaldoAlmacen], FILTER(ALL(Movimientos),Movimientos[Articulo] = _artical && Movimientos[Mov]<>"2.4 Bonificacion Venta") ,FILTER(ALL(Calendario), Calendario[Date]<MIN(Calendario[Date])))

RETURN totalant

_Ending Balance date=

VAR _index = SELECTEDVALUE(Movimientos[UltimoCambio])

VAR _artical = SELECTEDVALUE(Movimientos[Articulo])

--var totalant= CALCULATE([SaldoAlmacen], FILTER(ALL(Movimientos),Movimientos[Articulo] = _artical) ,FILTER(ALL(Calendario), Calendario[Date]<MIN(Calendario[Date])))

VAR total= CALCULATE([SaldoAlmacen],FILTER(ALLSELECTED(Movimientos),Movimientos[UltimoCambio] <= _index && Movimientos[Articulo] = _artical))

RETURN total

Best Reguards