Forum Discussion
Aggregate rows on months with DAX
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
I have a Dates table, which has the following content:
Date Month TTBA (Aggregation)
2018-06-01 6 1 5
2018-06-08 6 4 5
2018-07-08 7 5 12
2018-07-09 7 7 12
2018-08-05 8 3 3
2018-09-15 9 2 2
The rightmost column is what I am trying to add.
I am not sure what you mean, but that is essentially what I do, but I need to filter it in a good way, so it doesn't sum over the entire column. Also, note, I'm not doing a visual; I'm doing some "date calculations", so I try to make it a measure.
Ah ok.
Try the following:
Aggregation = CALCULATE(sum(Dates[TTBA]),ALLEXCEPT(Dates,Dates[Month]))
Hope that helps,
Thanks
Alex
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!
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
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!