-
1. Re: Sum values based on row dimension and column dimension that has been spilt by quarter
Steve Taylor Jun 10, 2016 7:26 AM (in response to Trevor Richardson)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
Trevor Richardson Jun 13, 2016 8:53 AM (in response to Steve Taylor)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
Steve Taylor Jun 13, 2016 9:52 AM (in response to Trevor Richardson)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
Steve Taylor Jun 13, 2016 10:11 AM (in response to Trevor Richardson)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
Trevor Richardson Jun 13, 2016 10:28 AM (in response to Steve Taylor)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
Steve Taylor Jun 13, 2016 2:13 PM (in response to Trevor Richardson)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
-
SuperstoreTrevor.twbx 758.6 KB
-
-
7. Re: Sum values based on row dimension and column dimension that has been spilt by quarter
Trevor Richardson Jun 14, 2016 6:50 AM (in response to Steve Taylor)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
Steve Taylor Jun 14, 2016 7:31 AM (in response to Trevor Richardson)1 of 1 people found this helpfulYou're welcome, sorry I couldn't solve the problem.
-
9. Re: Sum values based on row dimension and column dimension that has been spilt by quarter
Toby ErksonJun 14, 2016 7:38 AM (in response to Trevor Richardson)
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.