Filtering error with CALCULATE function

[edlng]

Hello !

I encounter a problem when creating a mesure with the CALCULATE function and filters. I want to have the share of electricity and gas in the total reduction (this part is OK); and then calculate the sum of both reductions.

This is what I have now:

But on my Excel table, the sums of each category are:

Electricity = -255.313,96€

Gas = -52.715,53€

The correct calculation should be (N1)+(N2)+(N3)=R
But here, PowerBI isn't taking into account that I have negative numbers; and is not adding brackets before the numbers.

Would you have an idea on how to achieve that?

To let you know, these are my measures:

• Reduction € = ([Consumption_P2]-[Consumption_P1])*[Avgprice_P1]
• Reduction electricity = CALCULATE([Reduction €], FILTER(Energy, Energy[Energy] = "Electricity"))
• Reduction gas = CALCULATE([Reduction €], FILTER(Energy, Energy[Energy] = "Gas"))

Thank you!

[maruthisp · Accepted solution]

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

[v-ssriganesh · Accepted solution]

Hi @edlng,
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.

[v-ssriganesh · Accepted solution]

Hi @edlng,

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.

[edlng]

Thank you @maruthisp for the solution, and @Ashish_Excel, @bhanu_gautam, @v-ssriganesh for your answers.
I was trying to figure out how to solve something else, because these DAX work for a comparison N/N-1, but not for other date periods. 

[v-ssriganesh · Accepted solution]

Hi @edlng,
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.

[maruthisp · Accepted solution]

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

[bhanu_gautam]

@edlng To achieve the correct calculation of the sum of reductions for both electricity and gas, you need to ensure that Power BI correctly handles the negative numbers and sums them up properly. 

Reduction € = ([Consumption_P2] - [Consumption_P1]) * [Avgprice_P1]

Reduction electricity = CALCULATE([Reduction €], FILTER(Energy, Energy[Energy] = "Electricity"))

Reduction gas = CALCULATE([Reduction €], FILTER(Energy, Energy[Energy] = "Gas"))

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

[edlng]

Thank you @bhanu_gautam, 
Indeed the Total reduction takes into account the negative numbers per row, but it's more the total of each column that is a problem. 

The result I'm willing to have for Total reduction is -308.029

Is there a way of changing the sum parameters per column?

[Ashish_Excel]

Hi,

How did you arrive at -308.029?  You may have to use the SUMX() function.  Share the download link of the PBI file.  Show the problem and expected result there.