Pivot Table Order of Columns

marcel_luthi

By default when you add a field to a pivot table as a column, it'll do all your Values calculations under each series before proceeding to calculate them for the next. Ex:

But sometimes you want each calculation together and then broken down by each series, so the one at the top would look like something like:

Since we don't know how many different series might be present, building a beast mode for each one is not feasible, and since this needs to respond to dashboard filters I cannot preaggregate in ETL either. Any ideas?

MichelleH

@marcel_luthi You're right, it does seem like there should be a setting for that. It sounds like a good candidate for the ideas exchange.

MichelleH

@marcel_luthi What if you used the Unpivot ETL tile to collapse the "Name" field so that you can use a single value field and add Name as the first column field?

marcel_luthi

Thanks @MichelleH, will this work even if I need the data shown to be responsive to card/dashboard filters? My data is not pivoted by name in the dataset, I do have Name as a column, but not really familiar with the Unpivot Tile functionality TBH, so I could be overlooking something there.

Also, to make it easier, they decided that this same view not only needs to be able to display things by Name as Rows, but also want to change it to see it by Region and Subregion (which I'll handle with a Variable), so was really hoping there to be a setting for the pivot table on how the grouping would take place 😅.

MichelleH

@marcel_luthi You're right, it does seem like there should be a setting for that. It sounds like a good candidate for the ideas exchange.

marcel_luthi

Thanks @MichelleH, I just submitted this for Ideas Exchange!