Forum Discussion

feralvarez994's avatar
feralvarez994
Icon for Helper II rankHelper II
3 years ago

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

 

ROI SUCURSAL =
-- CALCULATE AVERAGE RETURN ON INVESTMENT PER SUCURSAL
var _roipromxsuc =
CALCULATE(
    AVERAGEX(
        VALUES(SUCURSALES[SUCURSAL]),
        [ROI]),
    AUTOS[ROI 2] <> 0)
   
return _roipromxsuc
 
Im also attaching my PBI FILE  if anyone can help me

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi feralvarez994 

    please try

    ROI SUCURSAL =
    CALCULATE (
    AVERAGEX ( VALUES ( SUCURSALES[SUCURSAL] ), [ROI] ),
    AUTOS[ROI 2] <> 0,
    ALL ( SUCURSALES[SUCURSAL] )
    )