Forum Discussion
Anonymous
1 year agoNot applicable
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 ...
- 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]) ) ) )
rohit1991
1 year agoSuper User
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])
)
)
)