topic aggregate series in DAX Commands and Tips

aggregate series

borniex — Sun, 16 Jul 2023 14:34:43 GMT

Hello,
I want to aggregate series, I am attaching a picture of my idea, does anyone have an existing solution?
Thanks for your help!! 🙂

gregoliveira — Sun, 16 Jul 2023 21:47:37 GMT

Hi.

You will need to read about the windows function (https://www.sqlbi.com/articles/introducing-window-functions-in-dax). I have never used it, but I understand that it can deal with you scenario.

Greg Oliveira
https://www.linkedin.com/in/gregomelo/

v-zhangti — Wed, 19 Jul 2023 06:08:37 GMT

You can try the following methods.

Column:

previous part = CALCULATE(MAX('Table'[part]),FILTER('Table',[id]=EARLIER('Table'[id])-1))

judgement = IF([part]=[previous part],1,0)//Determine if the part is the same as the previous id

First part = IF([judgement]=0,1,0)

Max ID = CALCULATE(Max('Table'[id]),FILTER('Table',[id]<=EARLIER('Table'[id])&&[First part]=1))

Sum value = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Max ID]))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

borniex — Sun, 23 Jul 2023 16:19:14 GMT

Thank you very much, I was interested in this solution! Elegant, I thought more complicated 😄