Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX Problem to Aggregate a Filtered SUM

Hi All,

 

A DASHBORD provides information on Total Demand by Report's Months. There are several reports staked one over the other, and to compare demand differences of a past month report to current month report we use DAX. DAX aggregates SUMs filtered by report Month. So that Demand Curr (Month 0) is 192M and Demand Qty M-1 (Month -1, Last Column) is 201M.

 

The two months show a difference of ~9.7Millions (at the bottom) under the col Delta.

 

 

To verify that the sum was correct, I exported the same underlying data to an excel file:

  1. The data under  Demand Curr aggregates fine.
  2. But the aggregation on Month -1 is wrongly computed by DAX (see second pic below). The sum aggregation in the exported file is of 188.6M compared to 201M produced by DAX.

It seems that DAX has some difficulties with filtered high volume data; though it is weird that it does it well in the first month, but not for the second...

 

 

 

 

Details on the DAX formulae:

 

DAX:

Month of the report:

[REPORT_MONTH]: contains months 0,-1,-2,-3, etc. and records are tied to a date row/col.

 

Month0:

_Month = MAX('Lycos Extract MALO_NODE_FORECAST_SKU_MATRIX_TEST'[REPORT_MONTH])
Month -1
_Month -1 = [_Month]-1
 
Demand on the current month:
_Demand Curr =
var z=[_Month]
RETURN
CALCULATE(SUM('Lycos Extract MALO_NODE_FORECAST_SKU_MATRIX_TEST'[Total Demand])|'Lycos Extract MALO_NODE_FORECAST_SKU_MATRIX_TEST'[REPORT_MONTH]=z)
 
Demand on the month -1:
_Demand QTY M -1 =
Var z=[_Month -1]
RETURN
CALCULATE(SUM('Lycos Extract MALO_NODE_FORECAST_SKU_MATRIX_TEST'[Total Demand])|'Lycos Extract MALO_NODE_FORECAST_SKU_MATRIX_TEST'[REPORT_MONTH]=Z)
 
Thanks in Advance,

David Maradiaga
Dashboards
Data Modeling

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    Without sample data, I think it's hard to figure out what the problem is.

     

    I think you can try to remove most of dimensions, just keep the dimension which only have few values, then you will find why the total is different than sum of every rows.

    If not, you may need to track the details I think.

     

    Thanks.

    Aiolos Zhao