Incorrect total

[Raavë]

Hi, everyone.

In this table, Balance is a measure of subtraction between Purchases[Total_quantity] and Received[Net_volume] tables. The issue here is the total. It's subtracting the total of Volume received (which takes its data from Received[Net_volume]) from Contracted quantity (which takes its data from Purchases[Total_quantity]), which results in an incorrect total.

I'm trying to make the Balance total the sum of the values in the Balance column ignoring negative values, but with the functions I've already found using SUMX and CALCULATE, I haven't managed to solve the problem.

In the image, I'm using the standard Power BI measurement.

[Anonymous · Accepted solution]

Hi,@Raavë I am glad to help you.
Hello,@LeandroDeodato ,@Greg_Deckler ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
You can create a helper column or use another existing column as a filter for the ISINCPOED function.
Sorting the results of calculations to discuss the TOTAL row and other common rows
Here I have created a sorted row index (via power query)
This is used as a hierarchical determinant to actively modify the results displayed by total
Here is my test data:

measures I created:

Contracted quantity = 
SUM('Table'[C_Contracted quantity])

Volume  received = 
SUM('Table'[C_Volume  received])

Balance = [Contracted quantity]-[Volume  received]

M_Blan_result = IF(ISINSCOPE('Table'[Index]),
[Contracted quantity]-[Volume  received],
SUMX(
    FILTER(
        'Table',
        [Balance] > 0
        ),
    [Balance]
    )
)
//Use HASONEVALUE()or ISINSCOPE() determine the hierarchy of the current MEASURE calculation

Could you share model data or examples of your PBIX files (without sensitive data). You can upload your files using onedrive, google drive, we transfer or similar links.
This will help to solve your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Raavë First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[LeandroDeodato]

The total will fllow the same logic as the rows, so in this case, balance total = contracted quantity total - volume received total. you can hide this total and use mensures in a card to show the correct value.