Forum Discussion
Incorrect Measure Results
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.
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.
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.