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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
Anonymous
Not applicable

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

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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.