Forum Discussion
Custom Stepped Layout??
After much testing, I decided not to use Greg_Deckler's MM3TRR measure for this specific issue. MM3TRR allows for using different aggregations (sum, average, min, max) at each different subtotal level. While the measure is clearly very handy for creating that type of custom subtotals, it's not the feature I needed for this issue.
My custom subtotals all needed to be sums. The wrinkle came in because I needed to, for lack of a better term, "pick and choose" which line items should be included in each subtotal in the first place - based on a combination of values from two different fields, not only one.
In the end, all I had to do was create a custom field on my fact table concatenating the two target fields with a delimiter, then reference that concatenated field in my structure table instead of each field separately.
If anyone's interested in knowing more details, tag me and I'll share. I'm still thankful to Greg because his suggestion set me on the path of thinking about the problem from a different lens, which ultimately led to finding and correcting the flaw.
Hi Greg_Deckler. I've been working on this all day but no solution yet. Based on your sample data set, I can tell conceptually that the MM3TRR measure will work for mine, but I haven't figured out how yet.
First I tried linking the measure to my original "structure table", which only included "Category 1". When that method didn't work, I added "Category 2" information to the table thinking it would fill in a gap. Still off. At this point, I still need to test this issue a couple more ways before it officially becomes a road block. I can see the pieces are there, just can't make them click yet. I'll check back in after the next round of testing.
Thanks again for being on here and providing your expertise.
After much testing, I decided not to use Greg_Deckler's MM3TRR measure for this specific issue. MM3TRR allows for using different aggregations (sum, average, min, max) at each different subtotal level. While the measure is clearly very handy for creating that type of custom subtotals, it's not the feature I needed for this issue.
My custom subtotals all needed to be sums. The wrinkle came in because I needed to, for lack of a better term, "pick and choose" which line items should be included in each subtotal in the first place - based on a combination of values from two different fields, not only one.
In the end, all I had to do was create a custom field on my fact table concatenating the two target fields with a delimiter, then reference that concatenated field in my structure table instead of each field separately.
If anyone's interested in knowing more details, tag me and I'll share. I'm still thankful to Greg because his suggestion set me on the path of thinking about the problem from a different lens, which ultimately led to finding and correcting the flaw.