Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Grouping by a column while doing the calculation inside the DAX.

This is the table output result of a custom query(Import Mode) This data is currently used in the below DAX. While doing the below calculation inside the CALCULATE we need to group by HsCode ...
Need Help
Tips and Tricks
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi Anonymous ,

    To apply your calculation grouped by HsCode, you’ll need to wrap your calculation in SUMX over a grouped table, so each group (by HsCode) calculates independently and then aggregates.

    Modified DAX with Grouping by HsCode:

    MSR_CBAM_Value_Certificate_Cost_All_Actual =
VAR SelectedThresholdType = SELECTEDVALUE('Threshold Unit'[Threshold Unit])
RETURN
SUMX(
    VALUES(CBAM_Value_Volume_Summary[HsCode]),
    VAR CurrentHsCode = CBAM_Value_Volume_Summary[HsCode]
    RETURN
        CALCULATE(
            (
                (
                    SUM ( CBAM_Value_Volume_Summary[Annex_I_Default_specific_embedded_emissions] )
                        - (
                            SUM ( CBAM_Value_Volume_Summary[CBAM_Benchmark] )
                                * ( MAX ( CBAM_Value_Volume_Summary[FactorPercentage] ) / 100 )
                        )
                )
                * ( SUM ( CBAM_Value_Volume_Summary[ImportVolume] ) * MAX ( CBAM_Value_Volume_Summary[Annual_Factor] ) )
            )
            * [MSR_CBAM_Threshold_Value_Price_Actual],
            FILTER (
                CBAM_Value_Volume_Summary,
                CBAM_Value_Volume_Summary[HsCode] = CurrentHsCode &&
                CBAM_Value_Volume_Summary[TotalCustomsValue] > [MSR_CBAM_Threshold_Value_Price_Actual] &&
                CBAM_Value_Volume_Summary[ThresholdType] = SelectedThresholdType &&
                CBAM_Value_Volume_Summary[Year] = MAX(CBAM_Value_Volume_Summary[Year])
            )
        )
)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

MSR_CBAM_Value_Certificate_Cost_All_Actual =
VAR SelectedThresholdType = SELECTEDVALUE('Threshold Unit'[Threshold Unit])
VAR LatestYear = CALCULATE(MAX(CBAM_Value_Volume_Summary[Year]), ALL(CBAM_Value_Volume_Summary))
VAR ThresholdPrice = [MSR_CBAM_Threshold_Value_Price_Actual] -- Assume this is a scalar measure
RETURN
SUMX (
FILTER (
SUMMARIZE (
CBAM_Value_Volume_Summary,
CBAM_Value_Volume_Summary[HsCode],
"EmbeddedEmissions", SUM ( CBAM_Value_Volume_Summary[Annex_I_Default_specific_embedded_emissions] ),
"Benchmark", SUM ( CBAM_Value_Volume_Summary[CBAM_Benchmark] ),
"FactorPercentage", MAX ( CBAM_Value_Volume_Summary[FactorPercentage] ),
"ImportVolume", SUM ( CBAM_Value_Volume_Summary[ImportVolume] ),
"AnnualFactor", MAX ( CBAM_Value_Volume_Summary[Annual_Factor] ),
"TotalCustomsValue", SUM ( CBAM_Value_Volume_Summary[TotalCustomsValue] ),
"ThresholdType", MAX(CBAM_Value_Volume_Summary[ThresholdType]),
"Year", MAX(CBAM_Value_Volume_Summary[Year])
),
[TotalCustomsValue] > ThresholdPrice &&
[ThresholdType] = SelectedThresholdType &&
[Year] = LatestYear
),
(
([EmbeddedEmissions] - ([Benchmark] * ([FactorPercentage] / 100))) *
([ImportVolume] * [AnnualFactor])
)
* ThresholdPrice
)