cancel
Showing results for
Did you mean:
Highlighted
New Member

## 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)

Super User I

## Re: DAX Problem to Aggregate a Filtered SUM

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!