Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MarceloH10
Frequent Visitor

Issues with level of detail calculation using a relationship model

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]
    )
)
 
 
 This is what I get: 
 
MicrosoftTeams-image (9).png

 

This is what I need: 

MicrosoftTeams-image (10).png
 
Any help will be appreciated! 

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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?

vyiruanmsft_0-1692865216984.png

vyiruanmsft_1-1692865465649.png

 

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MarceloH10
Frequent Visitor

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])
)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors