SUMX tables with many-to-many relationship

[Anonymous]

Hi, I am trying to implement a Sumx  formula using two tables that have a many to many relationship.

Also I have two more tables that will be used as slicers("Semana" and "Moneda") for the table "TipoCambio1".

The formula I was using is:

VAR VENTAS = CALCULATE(SUMX(Hechos_Comercial,Hechos_Comercial[VentaNeta_ML]* RELATED(TipoCambio1[Ratio])),Hechos_Comercial[EsVenta]=1, FILTER(Calendario,Calendario[Semana]=[vMaxSemanaDias] && Calendario[Año] = [vMaxAño]), FILTER(TipoCambio1, TipoCambio1[TCAñoSemana] = [vAñoSemanaUsar]))

And the error that is showing is:
The column 'TipoCambio1[Ratio]' either doesn't exist or doesn't have a relationship to any table available in the current context.

I have also tried to use Treatas function without any luck.

How Can I achieve the sumx? 

Please see below the relationship model and how they are connected:

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

As far as I know , the related function can't be used in many to many relationship. It is used in many to one relationship to get value from one side.

In your situation. you need to use relatedtable function:

VENTAS =
CALCULATE (
    SUMX (
        Hechos_Comercial,
        Hechos_Comercial[VentaNeta_ML]
            * SUMX ( RELATEDTABLE ( TipoCambio1 ), TipoCambio1[Ratio] )
    ),
    Hechos_Comercial[EsVenta] = 1,
    FILTER (
        Calendario,
        Calendario[Semana] = [vMaxSemanaDias]
            && Calendario[Año] = [vMaxAño]
    ),
    FILTER ( TipoCambio1, TipoCambio1[TCAñoSemana] = [vAñoSemanaUsar] )
)

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

Best Regards,

Dedmon Dai