Issues with level of detail calculation using a relationship model

[MarceloH10]

I have 2 tables related. Table A (Maestro de partes) is the dimensional table, where each SKU code (NOPARTE) and brand group (CodMarca) is located. Table B (Liq 3M) is the fact table, stores the transacction at the SKU and date level from each import process in the last 3 months.

I need to calculate the sum of the import costs over the FOB factory price at the brand group level. I also need this value to remain the same whether I disaggregate it per SKU or brand group. Here is the formula I'm using considering the filters I need for this calculation: 

Factor de importacion = CALCULATE(

    ([Total costos de importacion]/SUM('Liq 3M'[FOB unit liquid]) + 1),

    FILTER(

        'Liq 3M',

        'Liq 3M'[Via transporte] = 2 &&

        'Liq 3M'[Datediff aplicacion] > 0 &&

        'Liq 3M'[Datediff aplicacion] < 4

    ),

    ALLEXCEPT(

        'Maestro Partes',

        'Maestro Partes'[CodMarca]

    )

)

https://1drv.ms/u/s!Ar7k80093sB_gckbSsUJKl9osviTWw?e=VfMKnh

 This is what I get: 

This is what I need: 

Any help will be appreciated! 

 

[v-yiruan-msft]

Hi @MarceloH10 ,

According to your description and screenshot, it seems that you want to get the result as shown in below screenshot. But as checked the value of field [NOPARTE] in the table 'Maestro Partes', I can't find any value with 'SKU1','SKU2' and 'SKU3' etc. Is there anything misunderstand in my side?

Best Regards

[MarceloH10]

These are some formula examples that I have already tested: 

1.  

Factor importacion marca = 
VAR costos_filtrados = 
CALCULATE(
    ([Total costos de importacion]/SUM('Query liquidaciones R2'[FOB unit liquid])) + 1,
    FILTER(
        'Query liquidaciones R2',
        'Query liquidaciones R2'[Via transporte] = "2" &&
        'Query liquidaciones R2'[Datediff aplicacion] > 0 &&
        'Query liquidaciones R2'[Datediff aplicacion] < 4
    )
)

RETURN
CALCULATE(
    costos_filtrados,
    ALL('Query inventarios'),
    VALUES('Query inventarios'[CodMarca]),
    VALUES('Query inventarios'[Numero de Parte]),
    'Query liquidaciones R2'
)

2. 

Factor importacion marca = 
VAR costos_filtrados = 
CALCULATE(
    ([Total costos de importacion]/SUM('Query liquidaciones R2'[FOB unit liquid])) + 1,
    FILTER(
        'Query liquidaciones R2',
        'Query liquidaciones R2'[Via transporte] = "2" &&
        'Query liquidaciones R2'[Datediff aplicacion] > 0 &&
        'Query liquidaciones R2'[Datediff aplicacion] < 4
    )
)

RETURN
SUMX(
    VALUES('Query inventarios'[Numero de parte]),
    CALCULATE(
        costos_filtrados,
        ALL('Query inventarios'),
        VALUES('Query inventarios'[CodMarca]),
        'Query liquidaciones R2'
    )
)

3.

Factor importacion marca v2 =

VAR test =

SELECTCOLUMNS(

    SUMMARIZE(

        FILTER(

            'Query liquidaciones R2',

            'Query liquidaciones R2'[Via transporte] = "2" &&

            'Query liquidaciones R2'[Datediff aplicacion] > 0 &&

            'Query liquidaciones R2'[Datediff aplicacion] < 4

        ),

        "Total Costos", [Total costos de importacion]

        ),

        "Costos Totales",[Total Costos]

        )

VAR test2 =

SELECTCOLUMNS(

    SUMMARIZE(

        FILTER(

            'Query liquidaciones R2',

            'Query liquidaciones R2'[Via transporte] = "2" &&

            'Query liquidaciones R2'[Datediff aplicacion] > 0 &&

            'Query liquidaciones R2'[Datediff aplicacion] < 4

        ),

        "Costos FOB", SUM('Query liquidaciones R2'[FOB unit liquid])

        ),

        "FOB",[Costos FOB]

        )

RETURN

CALCULATE(

    DIVIDE(test,test2),

    ALL('Query inventarios'),

    VALUES('Query inventarios'[CodMarca])

)