calculate balance from debit credit fields

[aqsasaleem]

I am calculating balance from debit and credit columns. but it's showing the same value. debit should be added and credit should be subtracted from balance. any solution?

[Dangar332]

Hi, @aqsasaleem 

as i understand your problem to solve you have one column which indicate index or column where we compare with same column for continuos asc or desc manner

for that add INDEX Column to your table using power query editor

then use below code for balance column

column = 
var a= debit[Index]
var b = debit[debit]
var c= SUMX(FILTER(debit,debit[Index]<=a),debit[debit])
var d = SUMX(FILTER(debit,debit[Index]<=a),debit[credit])
return
c-d

@aqsasaleem 

for measure calculation

try below

Measure 8 = 
var a = SUMX(FILTER(ALL(debit),debit[Index]<=MAX(debit[Index])),debit[debit])
var b = SUMX(FILTER(ALL(debit),debit[Index]<=MAX(debit[Index])),debit[credit])
return
CALCULATE(a-b,debit[Index])

[123abc]

If you want to calculate the balance by adding the debit amounts and subtracting the credit amounts in Power BI, you can create a measure or calculated column depending on your requirements.

Here's a step-by-step guide to help you create a balance calculation:

Using DAX Measure:

1. Open your Power BI Desktop and go to the "Modeling" tab.
2. Click on "New Measure".
3. Enter the following DAX formula to create a measure for the balance:

DAX MEASURE:

Balance = SUM('YourTableName'[Debit]) - SUM('YourTableName'[Credit])

1. Replace 'YourTableName' with the name of your table and [Debit] and [Credit] with the appropriate column names in your dataset.
2. Press Enter to create the measure.

Once you create this measure, you can add it to your visuals (like tables, charts, etc.) to see the calculated balance.

Using DAX Calculated Column (If Needed):

If you want to have this as a static value in each row rather than a dynamic measure, you can add a calculated column as follows:

1. Go to the "Modeling" tab.
2. Click on "New Column".
3. Enter the following DAX formula to create a calculated column:

DAX MEASURE:

Balance = 'YourTableName'[Debit] - 'YourTableName'[Credit]

1. Again, replace 'YourTableName', [Debit], and [Credit] with your actual table and column names.

However, be cautious when using calculated columns as they can increase the size of your data model, especially if the dataset is large. In most cases, using a measure is more efficient for these types of calculations.

After adding the measure or calculated column, you should see the balance calculated correctly based on the debit and credit values for each row in your dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[aqsasaleem]

I am already using almost same formula but it returns same value. it's not adding and subtracting values 

[123abc]

If you're using a formula that should ideally result in a balance (adding debit and subtracting credit) but it's giving you the same value, a few things might be happening:

1. Data Type Issues: Ensure that the data types for the Debit and Credit columns are numeric (like Decimal or Whole Number) and not text or other types.

2. Data Model Relationships: If you're summing up Debit and Credit from different tables based on relationships, ensure that the relationships are correctly set up. Incorrect relationships can lead to incorrect results.

3. Filtered Context: Depending on where and how you're using the measure or calculated column, the context might be causing the issue. For example, if you're looking at a specific category or time frame, it could impact the results.

Troubleshooting Steps:

1. Check Data Types: Ensure that both Debit and Credit columns are numeric. If they are stored as text, you may need to convert them to numbers using the VALUE() function in DAX.

2. Test the Formula:

   • Create a simple table or visualization where you display the SUM of Debit and Credit columns individually to see if they sum up as expected.
   • Test the balance formula with a simple table or card visualization to see if it calculates the balance correctly.

3. Use DAX to Debug: You can break down the calculation to debug. For instance, create individual measures to calculate the total debit and total credit to ensure they are being computed correctly:

Total Debit = SUM(YourTableName[Debit])
Total Credit = SUM(YourTableName[Credit])

Then, create another measure to calculate the balance:

Debug Balance = [Total Debit] - [Total Credit]

1. This will help you isolate where the issue might be occurring.

2. Review Relationships: Ensure that the relationships between tables (if applicable) are correctly set up. Check for any circular dependencies or incorrect relationship types.

3. Check Filters: Ensure that there are no filters or slicers applied that might be causing the unexpected behavior. You can temporarily remove filters to see if that impacts the calculation.

4. Data Preview: Double-check a sample of your data to ensure there are no anomalies or unexpected values in the Debit and Credit columns that might be causing the issue.

By systematically going through these troubleshooting steps, you should be able to identify where the problem lies and correct it. If you're still facing issues, you might want to share more specifics about your data model, relationships, and formulas for a more targeted solution.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.