By Group - Subtotal

Can you provide the original source data, please?

If you have the 2302 version of 365, you should have the GROUPBY formula function:

=GROUPBY(Table1[[Area]:[Core?]],Table1[Spend],SUM,,2)

Which version of 365 do you have? Is it 2302?

OK - if you can run update and get it up to 2402 (which is the |February release), you should be able to use this:

=GROUPBY(C2:D102,E2:E102,SUM,,2)

GROUPBY is for Insider releases

Originally Posted by Fluff13

AliGW I am using v2401 & do not have groupby yet. As far as I know it's still only on the beta channel.

No - it has been released on 2402, apparently (as I said above), so you'll get it with that release. 2403 is the beta channel version at the moment.

Originally Posted by barrywhitelaw

Wouldn't version 2401 be later than 2302? Looking on the release notes, 2302 was March last year?

Sorry - that was a typo. I meant 2402, which is this month's release. 2302 was rolled out from February last year.

I'm not sure I recommend it, but it can be done in PQ - example in the attached version of your file, which will probably need tweaking for your actual datasets, so I've tried to keep the steps separate for simplicity. The M code for your sample is:

Please Login or Register  to view this content.

You were asked (several times by Ali) to produce the source data but didn't. I have added a few more rows to simulate source data.

Formula version, with conditional formatting to highlight subtotal and total rows. Title row was applied manually.

Formula:

Please Login or Register  to view this content.

Thank you

FYI, source data was provided in step #4! I can still see it, can't you?

thanks
Barry

Apols. You probably have this sorted already, but as stubbornly possessive as a dog with bone, I wanted to get this to work.

Formula:

Please Login or Register  to view this content.

Thank you. I honestly envy anyone that can look at code like this an understand anything it's doing :-D.
I tried to apply it to my live data, but get error messages every time, so it'll me doing something wrong. Seeing it worked on the sample version, I'll keep at it and see if I can work out the issue.

Hopefully the update for Excel will come out soon as Ali said and it'll include the workaround.

thanks again

Does it work in the sample I provided if you change a few of the amounts in the raw data?

Providing the columns are the same in your real data as the amneded sample... it should work OK. I tweaked the formula so that you only have to adjust teh table name in two places (red)

Formula:

Please Login or Register  to view this content.

>>>IF<<< everything else is the same... it >>should<< work automatically

This is maybe beyond my abilities at present!
Looking at the file you sent back, to me looks like the formula is in cell B4. My data starts at A1, so selecting A1, pasting the formula in the function bar, amending the table name and clicking the tick. Nothing actually happens, and actually disappears out the formula bar. It'll be me doing something wrong I can guarantee it!

I appreciate the work you've done here, just maybe, for now, I need to find a less automatic route!

Reread my last post. If your data are in a Table, all you need to do is update tge table name in the formula *red) as indicated.

Thanks, I did read it.

My data is in a table called Summary.
I am selecting cell A1 (which is where my table starts) and pasting the formula into the formula bar. Is this correct?
I am then changing Table1 (you have in red) to Summary
Clicking the tick. At this point the formula bar empties, and nothing happens.

Please assume I'm an idiot. Are there any other steps I should have been doing? Don't assume I know what to do with the formula, however obvious it may be :-)

thanks

You don't paste the formula into the source table - you paste it into an empty cell where you want the results to start. Then you change the table references to point to the source table.

Glad to have helped.

If you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

A table was created in Power Query Editor and closed into a worksheet. Then using Glenn's code from post #29 and with help from AliGW in post #35, pasted the formula into an empty cell where I wanted the result to show. Amended the 2 Table1 refs in the coding to match my table name, and worked perfectly.