Forum Discussion

Feilin's avatar
Feilin
Helper II
8 years ago
Solved

Aggregate rows on months with DAX

I'm trying to aggregate a month value as a calculated column, but I'm struggling a bit how I should filter it. I have the following:   Month Time = CALCULATE( SUM( Dates[Time to be Agg...
Need Help
Show and Tell
  • alexei7's avatar
    alexei7
    8 years ago

    I'm probably not going to do it justice, but my understanding is that it's like the ALL function, which would remove all filter context (i.e. would sum everything in the table), except it keep the month as the filter context. Therefore, each row is totalled based on what is in the month column.

     

    I'm afraid i didnt understand your first question, you'll need to provide more data to show what you mean by these other different columns.

     

    Thanks

alexei7's avatar
alexei7
Continued Contributor
8 years ago

Ah ok.

 

Try the following:

 

Aggregation = CALCULATE(sum(Dates[TTBA]),ALLEXCEPT(Dates,Dates[Month]))

 

Hope that helps,

Thanks

Alex 

Feilin's avatar
Feilin
Helper II
8 years ago

Almost!

 

I actually have a few different columns (which I didn't mention for simplicity), and what it seems to do is actually what I want, but on all of those columns (i.e. take the aggregate over the period of the month that I want). How do I limit that to only the column selected (which I thought would be automatic...)?

Also, I don't really see how ALLEXCEPT solves the problem of aggregating over a month. Could you explain in a few sentences what it does?

 

Thanks!

  • alexei7's avatar
    alexei7
    Continued Contributor
    8 years ago

    I'm probably not going to do it justice, but my understanding is that it's like the ALL function, which would remove all filter context (i.e. would sum everything in the table), except it keep the month as the filter context. Therefore, each row is totalled based on what is in the month column.

     

    I'm afraid i didnt understand your first question, you'll need to provide more data to show what you mean by these other different columns.

     

    Thanks

    • Feilin's avatar
      Feilin
      Helper II
      8 years ago

      Oh, silly me. I just needed to except the Year as well, and it worked (it was the same number of years as additional columns, so I misinterpreted the results).

       

      Now it works, thanks!