cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simdav
New Member

Math bug report with Table Totals and TOTALYTD measure

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?

  PowerBIMathBug.PNG

 

4 REPLIES 4
WP
Frequent Visitor

Re: Math bug report with Table Totals and TOTALYTD measure

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
New Member

Re: Math bug report with Table Totals and TOTALYTD measure

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.

 

Super User IV
Super User IV

Re: Math bug report with Table Totals and TOTALYTD measure

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
WP
Frequent Visitor

Re: Math bug report with Table Totals and TOTALYTD measure

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.

 

 

 

 

 

 

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors