Aggregation discrepancies inside same table

[Anonymous]

I have one table 'ticket sales' containing the ticket_id, line_id, product_id, price, quantity, date and turnover. 

I want to analyze the total turnover of 2020.

First I put a filter visual filtering on date between '2020-01-01' and '2020-12-31'.

Then I create a table visual where I drag the turnover column, which should automatically aggregate the turnover by summing all values of the turnover column where the date is filtered. 

However, when I also drag the date column in the same table, so I get the turnover per day, the total row at the bottom of the visual differs from the total I get when I only have the turnover field in the table. 

See the screenshot below. Note that I redacted the concrete numbers. 

I also checked the DAX statements behind these two tables:

 // First value
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('sales'[date])),
      AND('sales'[date] >= DATE(2020, 1, 1), 'sales'[date] <= DATE(2020, 12, 31))
    )

EVALUATE
  SUMMARIZECOLUMNS(
    __DS0FilterTable,
    "SumTurnover", CALCULATE(SUM('sales'[turnover]))
    )

 // Second table
DEFINE
  VAR __DS0FilterTable = 
    FILTER(
      KEEPFILTERS(VALUES('sales'[date])),
      AND('sales'[date] >= DATE(2020, 1, 1), 'sales'[date] <= DATE(2020, 12, 31))
    )

 EVALUATE 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('sales'[date], "IsGrandateotalRowTotal"),
      __DS0FilterTable,
      "SumTurnover", CALCULATE(SUM('sales'[turnover])))
   

Could someone explain why there is a discrepancy between the two total values? Which one is correct?

[Anonymous · Accepted solution]

I managed to find the issue. 

It turned out that the turnover column contained a massively large number, once positive and once negative. Probably something that was wrongly scanned in the POS and then cancelled. 

As a result, the ticket looked like this:

+ 1 quadrillion

- 1 quadrillion

----------------

Total turnover = 0

After removing this ticket, the aggregation is correct. I suspect it has something to do with DAX that only allows for a certain number of digits for its numeric values. For instance 123456789 is 'rounded' to 123456000. 

Thank you @AlexisOlson. I managed to find it while I was experimenting with a dummy file because of your request. 

[VahidDM]

Hi @Anonymous 

Can you change the 'sales'[date] <= DATE(2020, 12, 31) to 'sales'[date] <DATE(2021, 1, 1) then check the result?

It might be because of the hours, add hours to your slicer date and check it again.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[AlexisOlson]

This looks strange indeed. Can you create a dummy file that reproduces this problem?