Is there a way to do a level of detail calculation?
I am trying to perform a level of detail calculation similar to the functionality available in other tools. My goal is to sum an entire column of a sheet and display that as a repeating value on each row of a column.
Here is an example of my intended goal. I know I could create a 2nd sheet in my workbook to groupsum my spend into one cell, but then I am not able to join it back up because there is no field to join on
Thanks in advance for the help!
-
Hi Jared,
If you're doing a GROUPBY on any column (I'm betting you're GROUPBY on the customer column) you're going to need a new sheet. My first instinct is to create the new sheet with a column containing the formula "GROUPBY(1)". Then use the COPY formula to copy the results from your previous sheet into the new sheet. This will group all your data into a single group, while keeping the GROUPBY order from the previous sheet. Then you can do a GROUPSUM, and it will sum all the values together as I think you want while also retaining the rest of the data.
Let me know if that doesn't get you moving forward!
-
Hi Alan Mark,
Thanks for the quick response! This doesn't seem to be working yet.
My Customer and Spend columns above are just regular fields (not groupby or anything) yet. I created another worksheet (named Sheet2) to do groupby(customer) and groupsum(spend) which aggregates the spend at the customer level - same results as before since it was one record each, but using the group functions.
On Sheet1, I have groupby(1), Sheet2!#customer, Sheet2!#spend, groupsum(Sheet2!#spend) and am getting an error.
The part that I'm not understanding is where you say to use the "COPY fomula to copy the results from previous sheet". Is that different than what I outlined?
Thanks again for your help.
-
Hi Jared,
COPY is not quite like just putting in the reference for the column.
You'd want COPY(#Sheet!Column).
This creates a copy of the data, including the current order, while not maintaining the group aggregation/series requirements. The issue is you can't use a group aggregation function on the same page as a group series function(with the exception of a single GROUPBY).
When you simply reference the column from the other sheet in the manner you are currently - you maintain these requirements.
Alan
-
Oh interesting, I hadn't used that function before.
So for my columns, I now have...
- On sheet2, I have GroupBy(#souce!Customer) | GroupSum(#source!spend)
- On Sheet1, I have GroupBy(1) | COPY(#Sheet2!Customer) | COPY(#Sheet2!Spend) | GroupSum(#Sheet2!Spend)
The final column groupsum(#sheet2!spend) is giving the same error as the last image of "#Sheet1!Total_Spend: Must be a group series function"
-
An alternative approach overall is this one:
- Add a column to your current sheet (I'll call this "Sheet1") that has a static String value of "Key".
- Sheet1 now has columns: Customer, Spend, "Key"
- Add a new sheet (I'll call this "Sheet2") with two functions: GROUPBY(Sheet1!Spend) and GROUPSUM(Sheet1!Spend)
- Sheet2 now has columns: Key, TotalSpend (and should have just 1 row since "Key" is static in Sheet1)
- Create a Join between Sheet1 and Sheet2 using the "Key" column as the Join criteria.
- The resulting Join Sheet should have columns: Customer, Spend, "Key", TotalSpend (from the GROUPSUM function in Sheet2)
Please sign in to leave a comment.
Comments
8 comments