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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors