Forum Discussion
Custom Stepped Layout??
Not sure if what I want to do is even possible...if so, it's definitely beyond my capabilities.
I'm trying to create a stepped-layout matrix with a few caveats:
- Caveat 1: Header rows
- Caveat 2: Customized subtotal rows
- Caveat 3: Certain rows where the second column of the stepped layout is "collapsed" so only one value is displayed
The desired outcome looks like this, with each caveat highlighted.
Note that in a given instance, Caveat 3 applies every time plus either Caveat 1 or 2. For the visual as a whole, however, all three caveats must be satisfied.
My data set is pretty basic with 4 columns.
When I use the standard Power BI method of creating the stepped-layout matrix, the visual is very limited.
These basic subtotals satisfy Caveat 2 because they are collapsed. However, they are not customized to group certain Cars (Caveat 1B) and no header rows are available (Caveat 1A). If I choose to "show items with no data", headers would show up (satisfying Caveat 1) but Aspect would also show up on every row (violating Caveat 2). Essentially, I can only meet one of the caveats using this method.
Next I created a "structure table" along with an associated "unpivoted" table that tells the main visual which values to include in each row - then used those to feed the visual.
The solution is getting closer. Headers and custom subtotals are there (Caveats 1 and 2), but neither of those line items is collapsed into a single row (Caveat 2). A couple examples:
- "All Cars" is a header row but also includes each value from the Aspect category (Power, Speed, Visual Appeal).
- Likewise, "Total Ford" and "Total Chevy" (both custom subtotals) are also broken out by each Aspect value.
This is where I'm stuck. Is there any way to get each header line and custom "total" line to display as single rows instead of being broken out by the Aspect category?
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.
4 Replies
Thank you Greg_Deckler ! At first glance, this looks promising. I'm nearing the end of my day but look forward to delving in more tomorrow to test the solution.
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.
