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 ...
  • rohit1991's avatar
    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])
                )
            )
    )