sum from multiple tables

[nicoenz]

Hi everyone,

I really need help on building an expression that i can't get it to work properly.

i have the following star schema (coloured fields are the connected ones).
The two unrelated tables (Dim_Proc_forecast_versions...) are there to calculate the same expression twice.

I want to compare two versions to determine the impact on each component for the selected "Material_Code" (one or more from the slicer on Dim_PH123). I need to obtain the final volume from the "SourceData" table, the amount per component from the "Exploded BOM" table, and the price from the "Procurement Forecast" table.

The expression should compute: Final volume * Amount * Price difference.

The formula I wrote works perfectly well on each component but the total is not OK. I am frying my brain but nothing I change works. Can you please help me?

"

Price Impact RM =

VAR SelectedVersion1 = SELECTEDVALUE(Dim_Procurement_forecast_versions_RM1[Version])

VAR SelectedVersion2 = SELECTEDVALUE(Dim_Procurement_forecast_versions_RM2[Version])

RETURN

SUMX(

    VALUES(Dim_PH123[Material_Code]),

    VAR FinalVolumeKG = CALCULATE(SUM(SourceData[Final_Volume_KG]))

    VAR Quantity = CALCULATE(SUM('Exploded BOM'[Amount]))

    VAR Price1 = CALCULATE(

        SUMX('Procurement forecast', 'Procurement forecast'[Price]),

        FILTER(

            ALL('Procurement forecast'[PriceType]),

            'Procurement forecast'[PriceType] = SelectedVersion1

        ),

        FILTER(

            ALL('Procurement forecast'[Version]),

            'Procurement forecast'[Version] = SelectedVersion1

        )

    )

    VAR Price2 = CALCULATE(

        SUMX('Procurement forecast', 'Procurement forecast'[Price]),

        FILTER(

            ALL('Procurement forecast'[PriceType]),

            'Procurement forecast'[PriceType] = SelectedVersion2

        ),

        FILTER(

            ALL('Procurement forecast'[Version]),

            'Procurement forecast'[Version] = SelectedVersion2

        )

    )

    RETURN

    FinalVolumeKG * Quantity * (Price1 - Price2)

)

"

See example below:
Volume for "Material 1" is 503 kg and for "Material 2" is 15 kg
The table i get is:

(the volumes are not right there)

Thanks for your help!!!!!!!

[nicoenz]

Hi @rajendraongole1 ,

thanks for your help!!!!. it doesn't work well. I created a simplified version of the file. I'd really appreciate it if you can take a look into it:
https://drive.google.com/file/d/1sqs3HbmrcoTz4pyNVYd5WyG0bLOp-ynt/view?usp=sharing 

Thanks!

nico

[rajendraongole1]

Hi @nicoenz - Adjusting the calculation to ensure the aggregation respects the total context.

Price Impact RM =
VAR SelectedVersion1 = SELECTEDVALUE(Dim_Procurement_forecast_versions_RM1[Version])
VAR SelectedVersion2 = SELECTEDVALUE(Dim_Procurement_forecast_versions_RM2[Version])

VAR FinalVolumeKG = CALCULATE(SUM(SourceData[Final_Volume_KG]))
VAR AmountPerComponent = CALCULATE(SUM('Exploded BOM'[Amount]))

VAR PriceDifference =
CALCULATE(
SUMX(
'Procurement forecast',
VAR Price1 = CALCULATE(
SUM('Procurement forecast'[Price]),
FILTER(
ALL('Procurement forecast'[PriceType]),
'Procurement forecast'[PriceType] = SelectedVersion1
),
FILTER(
ALL('Procurement forecast'[Version]),
'Procurement forecast'[Version] = SelectedVersion1
)
)
VAR Price2 = CALCULATE(
SUM('Procurement forecast'[Price]),
FILTER(
ALL('Procurement forecast'[PriceType]),
'Procurement forecast'[PriceType] = SelectedVersion2
),
FILTER(
ALL('Procurement forecast'[Version]),
'Procurement forecast'[Version] = SelectedVersion2
)
)
RETURN Price1 - Price2
)
)

RETURN
SUMX(
VALUES(Dim_PH123[Material_Code]),
FinalVolumeKG * AmountPerComponent * PriceDifference
)

This structure should maintain correct values for each component row and the grand total.

Hope this helps , and let me know if any