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

[pbiforum_123]

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 and then do the calculation earlier without grouping by we are considering the numbers but now this calculation has to work after grouping by the HsCode.

MSR_CBAM_Value_Certificate_Cost_All_Actual =

VAR SelectedThresholdType = SELECTEDVALUE('Threshold Unit'[Threshold Unit])

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[TotalCustomsValue]

            > [MSR_CBAM_Threshold_Value_Price_Actual] && CBAM_Value_Volume_Summary[ThresholdType]=SelectedThresholdType && CBAM_Value_Volume_Summary[Year]=MAX(CBAM_Value_Volume_Summary[Year])

    )

)

Please help me out on this, it is little urgent.

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.

[v-mdharahman]

Hi @pbiforum_123,

Thanks for reaching out to the Microsoft fabric community forum.

I understand that your existing calculation is working at a total level and not grouping by HsCode, which is now required for accurate per-category aggregation. You're essentially looking to apply the calculation individually per HsCode and then aggregate the result across all HsCodes.

You are absolutely on the right track and kindly go through the suggested approaches shared by @Divya_Shivanna

and @rohit1991  in the thread and check if your issue can be resolved.

I would also take a moment to thank @rohit1991, @Divya_Shivanna and @FBergamaschi for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[v-mdharahman]

Hi @pbiforum_123,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @pbiforum_123,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[pbiforum_123]

@v-mdharahman I got some solution we can close this

[v-mdharahman]

Hi @pbiforum_123,

I am glad that you got a solution of your issue. Kindly post the workaround which helped you and mark it as solution so that other community members facing the same issue can find it quickly.

Best Regards,

Hammad.

[v-mdharahman]

Hi @pbiforum_123,
We noticed there hasn’t been any recent activity on this thread. As you mentioned your issue has been resolved, so kindly post the workaround and mark it as solution and if you still need support, just drop a reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[rohit1991]

Hi @pbiforum_123 ,

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])
            )
        )
)

[Divya_Shivanna]

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
)

[FBergamaschi]

This is a DAX question, so please create a post in the DAX forum

And please make a clearere explanantion of the issue you are facing (show us the visual with the undesired result) and the result you would like to have.

Also,

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here