cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dmaradiaga New Member
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.

 

DAX Problem.JPG

 

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...

 

DAX Problem 1.JPG

 

 

 

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
1 REPLY 1
Super User I
Super User I

Re: DAX Problem to Aggregate a Filtered SUM

Hi @dmaradiaga ,

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors