Incorrect Measure Results

[kdurbin]

I have three measures that I created that are very simple.  Two of the measures are Calculates off a Debit/Credit field.  The third measure is the simple addition of the first two measures. 

When the data is presented in PBI, the first two measures calculate perfectly.  However, the third measure does not.  I have checked for filters on the measures/report and have even looked at the raw data.  When I segregate the data by other attributes in the table (for example by account which is one of 25 variables), PBI gives the correct calculation in the third measure 80% of the time.  The other 20% is off, inconsistently.

[AlexChen]

Hi,

I did a test using your measures and it works properly.

This is sample table:

Create measures “Credit_Totals”, “Debit_Totals”, “Balance” with your code.

This is the result:

You can see that all 3 Balance is correct. 

I think your dataset should be much complexed than this.

So I hope you can give a sample dataset and tell us which data is wrong.

Best Regards

Alex

[kdurbin]

I got it fixed.  The Balance measure was some sort of strange type of measure that had a different icon, so I deleted it and rebuilt it and it works fine.  Still can't explain what it was doing, but the data was fine all along.  Not sure why the first measure worked part of the time.

[KGrice]

Hi @kdurbin. Can you post the three measure definitions, as well as an example of where the third is off and what it should be? Even a table with the all three measures on it where we can see what the values are and how they're adding (or not adding) up would help. Depending on the complexity, it might also help to understand the data model more.

[kdurbin]

Unforutnately, I cannot share the data because it's financial in nature and contains transactional records.

Here are the three measures as they sit now.

Measure 1: Credit_Totals = CALCULATE(sum(tbl_Ledger[Tx_Amount]), tbl_Ledger[Tx_Type]="Credit")

Measure 2: Debit_Totals = CALCULATE(sum(tbl_Ledger[Tx_Amount]), tbl_Ledger[Tx_Type]="Debit")

Measure 3: Balance = [Credit_Totals] - [Debit_Totals]    

Measure 1 and Measure 2 calc correctly in all respects.  Measure 3 does not.  I have also build these three measures into a single measure such as:

Measure 4: Balance = (CALCULATE(sum(tbl_Ledger[Tx_Amount]), tbl_Ledger[Tx_Type]="Credit")) - (CALCULATE(sum(tbl_Ledger[Tx_Amount]), tbl_Ledger[Tx_Type]="Debit"))

This results in the same incorrect sum as measure 3.

I have narrowed down the area within the data using an attribute field called Tx_Account.  Within this attribute field, 19 of 24 values show a correct Measure 3 calculation.  The other 5 are incorrect.  The delta is not equal to any value I could find in the dataset.  Unfortunately these 5 attributes represent 85% of the total data, so it is probably coincidental.  Also, data-mining further would require analysis of each record in a table of 16K records (small, I know.)

FYI, for what its worth, the table is a Sharepoint list.

[KGrice]

What happens if you make a measure like Amount_Totals = SUM(tbl_Ledger[Tx_Amount]) and then use that in your other measures.

Measure 1: Credit_Totals = CALCULATE([Amount_Totals], tbl_Ledger[Tx_Type]="Credit")

Measure 2: Debit_Totals = CALCULATE([Amount_Totals], tbl_Ledger[Tx_Type]="Debit")

I think it will be the same but would like to confirm. I've seen where using or not using building blocks can affect the calculation, but I'm pretty sure that's only on AggregateX functions like SUMX.

You said the error is off inconsistently. Is it at least off in the same direction (e.g., all amounts too high or too low), or in any other pattern you can tell?

Other ways to troubleshoot and figure out what's going on could be DAX Studio, going through the evaluation steps in this reference card, or even putting it in an Excel pivot and drilling through to the specific rows making up the calculation.