Math bug report with Table Totals and TOTALYTD measure

[simdav]

Since the April update was pushed out, I am experiancing different types of math bugs in table totals and in Measures.  In the simple example below I created a small table with totals.  I exported the numbers to Excel and re-added them below, showing the totals are wrong.  I am having similar trouble with the TOTALYTD formulas not adding things up correctly.  Column C here is raw data for the period and column F is TOTALYTD, but they don't add up.  Any suggestions?

 

[WP]

Hi simdav,

Did you exclude a different evaluation context being active on the total level as a possible cause? Because by using formulas in DAX you can end up with lots of weird results using filter and row context. But when looking at the model, relationships and filters active for a specific field, the result is explained.

If you did not rule out dax effects, can you maybe show the formula you use to calculate with short description of tables in data model and relationships active?

[simdav]

Let's just discuss Column C not totaling correctly.  In the DB there is a field called OPEXBudget, just simple numbers, however all these rows are filtered out because of a table join and I need to add them back.  To do this I have created the following Measure which is in the table:

OPEX Budget = CALCULATE(sum(vReportingFinance[OPEXBudget]), ALLEXCEPT(tableA, tableA[field1], tableA[field2], tableA[Column_A_Measure]))

I am intentionally ignoring fild1 and field2 because they are report level filters which only apply to rows that don't have an OPEXBudget value.  I am ignoring Column_A_Measure because it's the row level distinction in the table.  Every row has a value for Column_A_Measure, so they should all be included in the total.

[WP]

Hi @simdav

So what I understand is you have two tables vReportingfinance and tableA. In the pivot table, you use a filter from tableA, which would filter out rows from vReporting finance. Therefore you want to ignore this filter by using allexcept in order for all rows from table A to be considered? Why do you use allexcept instead of all, because by using allexcept, you leave any possible filters on these three columns active? 

When looking closer to your sample picture I also spot some inconsistencies for both XL and PBI results. In columns E, F, G your sum is equal to the result of Q4, sometimes for both PBI and XL, sometimes only for XL. Also first and last column does not contain any discrepancies. What is difference between those two and other columns?

I am with @GilbertQ try to simplify and see what happens with the result. Also adding some more granularity to the rows, so you see the individual rows that are added, can help you identify causes.

[GilbertQ]

Hi @simdav

I have to agree with @WP, in that something within your DAX is causing the data to be filtered out, which is then causing the total to appear incorrect.

What I would suggest doing is first create a simple measure  "myMeasure = sum(vReportingFinance[OPEXBudget])"

Then put that into a table and make sure that you get the right results.

Once that has been validated then work on your OPEX Budget by first adding in the ALLEXCEPT with one field at a time. 

I have had a similar issue in the past and it took me a good while to figure it out. And in the end the DAX measure was doing what it was told, and it was the filter context that I was using that was causing it to be wrong.