Forum Discussion
CALCULATE AVERAGE PER CITY
Hi everyone, i have a table with cars (AUTOS) and im trying to calculate the average return on investment per buyer (COMPRADOR) and per local/city (SUCURSAL). I have already calculated the average ROI but i want to display a table with the buyers and the average of their city too. How can i calculate this? im struggling with some dax here.
ROI 2 =
-- CALCULAR EL PRECIO DE VENTA, LUEGO EL COSTO, EL COSTO ACTUALIZADO DEL AUTO SI ES QUE LO TIENE Y LOS GASTOS ADICIONALES
var pvta = AUTOS[Precio Venta]
var pcomp = AUTOS[Costo + IVA]
var pcompact = LOOKUPVALUE('COSTO INFLACIONARIO'[COSTO ACTUALIZADO], 'COSTO INFLACIONARIO'[INTERNO], AUTOS[AT_COAUTO], 0 )
var gastosadic = LOOKUPVALUE('GASTOS ADICIONALES (FACINT)'[COSTOS_ADICIONALES], 'GASTOS ADICIONALES (FACINT)'[INT_COAUTO], AUTOS[AT_COAUTO], 0 )
-- CALCULAR EL ROI SEGÚN SI TIENE PRECIO ACTUALIZADO O PRECIO INICIAL
var roi = (pvta - (pcomp + gastosadic)) / (pcomp + gastosadic)
var roiact = (pvta - (pcompact + gastosadic)) / (pcompact + gastosadic)
var result = if(pcompact = 0, roi, roiact)
return IF(result > 10 || result < -10 || ISERROR(result) || pvta = 0, 0, result)
The table AUTOS is related to the buyer, and the buyer leader cod is the city so it has a related SUCURSALES table too
For example, here i have selected the local TANDIL in the upper table that has a average roi of 12,14%. I want to display that value in the sellers of the lower table, so in its column ROI SUCURSAL it should display 12,14% in each row and in the total too.
Im using this measure but its not working
1 Reply
- tamerj1
Community Champion
please try
ROI SUCURSAL =
CALCULATE (
AVERAGEX ( VALUES ( SUCURSALES[SUCURSAL] ), [ROI] ),
AUTOS[ROI 2] <> 0,
ALL ( SUCURSALES[SUCURSAL] )
)