9 Replies Latest reply on Jun 14, 2016 7:38 AM by Toby Erkson

Sum values based on row dimension and column dimension that has been spilt by quarter

I have receipts as a number value as what shows up by quarter for a year and by business function. Each receipt will show up in only one quarter. I want to sum all of the receipts by business function even though they are in different columns from Q1 - Q4. Any ideas?

I have two row values-- business unit and receipt ID

I have one column Value -- receipt date received (split into quarters)

Then the result is how much money.

Again, I'm looking for a way to sum the different columns.

Message was edited by: Trevor Richardson Hi Steve, I added the attached document to give you a better understanding of my question above. Superstore assistance is the sheet I want help with. I need calculation 1 to report the correct values. Calculation 1 is an attempt to sum all of the first class- same day, second class, and standard class sales per customer by category. Therefore, there should be one answer for each category. EX: Furniture should report 7641.55 (unless I made some small math error adding) -- basically sum all of the column for the rows corresponding to each category. So sum 16 numbers for Furniture sum 36 numbers for Office Supplies and another 16 for technology.

Also in my real data some of the column row values are blank for example maybe no one in art decided to make second class shipping so that table row-column value is nothing -- not zero just nonexistent.

Message was edited by: Trevor Richardson

• 1. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Hello Trevor

Is there any way you can anonymise the data and attach a packaged workbook to this thread? Or mock up the problem using the superstore data supplied with tableau? It'll be miles easier to see what's going on and assist you.

Thanks

Steve

• 2. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Hi Steve, I posted what you asked for. THX!

• 3. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Thanks Trevor, I'll see if I can figure it out!

• 4. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Sorry Trevor, can I ask why you went for sales per customer to use in this calculation? from your original post you said you were looking to "sum all of the receipts by business function" and if you choose Sales as your measure, your calculation works:

Is there something in your data structure that requires you to create a calculated field like the Sales per customer and use that instead of summing the receipt values?

Steve

• 5. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

I didn't mean to make the sample data I sent you worthwhile to show others. I did not pick sales per customer for any apparent reason. I just have numbers exactly like in the sample data above and I need to be able to sum all of the values in the rows by the outside hierarchy or some dimension.

I am lost at what you're asking. I don't know why your numbers are different than mine. My numbers in the superstore data are in the tens and hundreds not tens of thousands. I just want someone to be able to show me how to sum the first 4 rows (4 columns wide) and give me a solution, the next 16 rows (4 columns wide) and the last 4 rows (4 columns wide). In my actual data I have a hierarchy and I have a report I have to build where I need the hierarchy display and the calculations divided into 4 columns and I need to add everything in the rows that are of the same "Category" in this case.

• 6. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Sorry Trevor, I didn't make it very clear what I was doing.

For your example you used Sales per customer which is a calculated field - SUM(Sales)/COUNTD([Customer Name]). This will behave differently within a Level of Detail calculation to a simple SUM(Sales) would. I switched the sales per customer field with the SUM(Sales) which is why the numbers are so different.

I asked whether your real data involved a similar calculation to Sales per customer or whether it was a simple SUM because if it was the latter, the calculation should work {FIXED [Category]: SUM(Sales)}

The sum of everything in the red box = \$724,000 and the sum of everything in the green box = \$719,047 and Calculation 1 will remain that value whether you expand the hierarchy or not.

If this isn't working with your data then please let me know how the field you're currently trying to SUM is calculated (i.e. what the formula is) and I'll have another look tomorrow morning.

Apologies for the poor explanation, I've attached your workbook with the edit.

Steve

• 7. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Steve thank you for all of your help I'm on to the next question:) Thank you again for everything

• 8. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

You're welcome, sorry I couldn't solve the problem.

1 of 1 people found this helpful
• 9. Re: Sum values based on row dimension and column dimension that has been spilt by quarter

Sounds like Excel would be the more appropriate application for a data table.  The benefit of this from your point of view is that you can use VBA to modify the individual cells as you programmatically see fit.  Tableau's specialty is visual charts.