Yes, I believe it is possible. Can you provide the Excel file that you used yo create that screenshot?
Here is one method:
Create three calculated fields:
IF MIN([Segment])=MAX([Segment]) THEN IF MIN([Product])=MAX([Product]) THEN SUM([Value])/[Segment Total] ELSE SUM([Value])/[Region Total] END END
Place the "Mix" field onto the sheet, and bring up the pill's Edit Table Calculation dialog. You will see a drop-down titled Calculated field, with the two options "Segment Total" and "Region Total".
- Set the Compute using for "Segment Total" to "Product"
- Set the Compute using for "Region Total" to "Segment"
This is done in the attached.
If your situation is different, please provide a sample data file that represents your situation.
mix.twbx 21.1 KB
That's awesome, Joe. Works perfectly. So the Min/Max logic simply tells you whether you're on a detail row or a sub-total row?
The Min/Max logic I used will work in this situation to determine where the calculation is being performed. Other situations, you may need a different calculation, or if there are situations where a Region has only one Segment, and the Segment has only one Product, then you will need additional logic to capture for those situations.
Is there a reason you use both Min and Max in the logic, when it appears that using "IF MIN([Segment])=MIN([Segment])" achieves the same outcome?
1 of 1 people found this helpful
This is an interesting one. When I first saw the question my reaction was "not possible", but that's always a dangerous thing to say.
The reason for the MIN/MAX logic is that Joe is using the fact that MN and MAX of a particular dimension are not the same to indicate that the current row is a (sub)total with respect to that dimension. But as Joe says, that breaks down if there is only one value for that dimension at any particular place in the hierarchy. So I would treat this approach with real caution - it would be very easy to build something that gives the right answer when you first test it, but proves to be susceptible to changes in data distribution over time.
I had a good look at how all of the available functions work with respect to subtotals, and I can't find any reliable general way of doing what you want (but just because I can't think of it doesn't mean there isn't a way, of course). I'll be interested in Joe's thoughts.
If you look in the tool-tips, you will see that the values of the relevant dimensions display as "All" for the subtotal rows. So if you could just get at that from the calculated field logic you would be able to do what you want. But unfortunately there doesn't seem to be any way to get at that. I think there's a need for an ISTOTAL() function to test whether or not you are on a sub-total row.
"MIN([Segment])=MIN([Segment])" would always return true, so I don't know of a situation where that would be useful.
"MIN([Segment])=MAX([Segment])" returns False when in the subtotal for "Segments".
Richard, yes this is a simplistic test, and an ISTOTAL() function would be very nice for situations like this, but you still need a way to determine which subtotal you are in. I believe it is possible, but I still do not have a working solution for detecting total of a single row.
I'm attaching a revised version of your mix.twbx file that shows that MIN/MIN produces the same results as MIN/MAX. It's possible I'm doing something wrong, but when I was deconstructing your initial post to make sure I understood it, I noticed that there didn't appear to be any difference between the two options, and that still looks to be the case.
mix.twbx 23.2 KB
The calculated fields are nested IF statements, so there are really 4 possible results, if you change:
IF MIN([Segment])=MIN([Segment]) THEN IF MIN([Product])=MIN([Product]) THEN "Yes" ELSE "No" END END
IF MIN([Segment])=MAX([Segment]) THEN IF MIN([Product])=MAX([Product]) THEN "Yes" ELSE "No" END END
IF MIN([Segment])=MIN([Segment]) THEN IF MIN([Product])=MIN([Product]) THEN 1 ELSE 2 END ELSE IF MIN([Product])=MIN([Product]) THEN 3 ELSE 4 END END
IF MIN([Segment])=MAX([Segment]) THEN IF MIN([Product])=MAX([Product]) THEN 1 ELSE 2 END ELSE IF MIN([Product])=MAX([Product]) THEN 3 ELSE 4 END END
You can see the possible values displayed in the attached.
In this data 3 is never a result, but theoretically could be (this is one of those situations Richard brings up, if 3 is a result, then there are other complexities to deal with).
Also notice how I am using a numeric measure instead of a text value, this is because text values cannot be displayed in a Grand Total or Sub Total.
mix_0_jm_edit.twbx 24.1 KB
Got it...yeah, I see how the logic behaves differently with numeric values. Thanks for the clarification.
I also played around with my own data and do see the issue when there is only a single row within any segment, but this is still a very helpful solution for the majority of the cases we deal with. Were Tableau to come out with an ISTOTAL() function, that would of course be stellar.
I almost mentioned Joe's point about differentiating sub-total from grand total when I was posting that last night. Maybe what is really needed is a TOTALDEPTH() function which returns 0 for detail rows, 1 for the first level of subtotals, 2 for the next level, etc.
I am not sure that would be the complete solution. Because turning on and off subtotals is at the pill level, but that calculation you propose would depend on the layout, and could break calculations if the layout changes (hierarchy, moving pills to different shelves, etc.). I do not know what the best answer would be, maybe another option in the Edit Table Calculation dialog for using a different partitioning setup that is based on what the Sub/Grand Total is being performed on (I am not sure if that makes any sense), but that may not work because some people want a sum at one level and an average at another. How do you determine what level of detail you are calculating at within a calculated field's formula? Your proposed calculation could give a number like an index, but do you specify what that order is? Would it be a hybrid of Level of Detail shelf and layout of pills on the other shelfs? It is a tough question, lots of potential situations and complexities (or maybe I am looking at the problem from the wrong viewpoint, making it more complex than it needs to be).
You are quite right - just knowing the depth still depends on assumptions about the layout and which subtotals are enabled, so that wouldn't be enough.
This gets quite complex quite quickly, doesn't it? From experimenting with the workbook you posted for Mike it's clear that the subtotal calculations operate within their own "automagically created" partitions (drop an instance of INDEX() onto the view and that is very obvious).
It feels to me as if there would need to be some way of exposing what is happening with these "automagically generated" partitions in order to take control of subtotal calculations properly. As with everything table calc related, it's actually a hard problem - so one of the questions has to be whether the problem we are trying to solve is significant enough to justify the extra complexity that the solution would add.
Yes, I agree, it may add complexity with limited benefit.
A possible solution that I was thinking of is to pad the data with an additional row to each possible level of detail, and use a table calculation filter to hide those rows.
If you can provide a data set set that the above formula does not work for, I think I can provide a working solution, or maybe some custom SQL that can be adapted to work in other situations.