Filtering error with CALCULATE function

Hi edlng,

Please try the below DAX expressions based on you rdescription provided.

1.For Reduction electricity:
Reduction electricity (sum rows) =
SUMX(
VALUES(Energy[YourGroupColumn]), // Replace with the grouping column in your visual
CALCULATE([Reduction €], Energy[Energy] = "Electricity")
)

2.If your table is grouped by for example, [Period]:
Reduction electricity (sum rows) =
SUMX(
VALUES(Energy[Period]), // Or the column you're grouping by
CALCULATE([Reduction €], Energy[Energy] = "Electricity")
)

3. Do the same logic for Gas:
Reduction gas (sum rows) =
SUMX(
VALUES(Energy[Period]),
CALCULATE([Reduction €], Energy[Energy] = "Gas")
)

4. Grand Total:
Total Reduction = [Reduction electricity (sum rows)] + [Reduction gas (sum rows)]

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Hi Anonymous,
Thank you for reaching out to the Microsoft fabric community forum.

I’ve reproduced your scenario in Power BI Desktop using a simplified dataset that mimics your structure (Electricity and Gas categories with consumption and pricing). I observed the same issue you described the column totals for your Reduction electricity and Reduction gas measures did not reflect the sum of row-level calculations as expected.

This is due to how Power BI evaluates DAX measures differently at the total level (it recalculates in the total context instead of summing the visible rows).

I rewrote the measures using SUMX to ensure they respect row-level context and aggregate properly at the total level.

Here's the DAX I used:

Reduction € =

SUMX (

    EnergyData,

    (EnergyData[Consumption_P2] - EnergyData[Consumption_P1]) * EnergyData[Avgprice_P1]

)

Reduction electricity =

    SUMX(

        FILTER(EnergyData, EnergyData[Category] = "Electricity"),

        ([Consumption_P2] - [Consumption_P1]) * [Avgprice_P1]

    )

Reduction gas =

    SUMX(

        FILTER(EnergyData, EnergyData[Category] = "Gas"),

        ([Consumption_P2] - [Consumption_P1]) * [Avgprice_P1]

    )

Total reduction = [Reduction electricity] + [Reduction gas]

I’ve attached a .pbix file for your reference.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

Hi Anonymous,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.