Is it possible to have multiple levels within a pivot table? I currently have the column header split out into two sub-columns, but is it possible to split those columns further?
Additionally, is it possible to only show columns and rows in a pivot table where a value in a certain value in a sub-column is negative? I have a pivot table and each column header has two sub-columns. If there are no negative values in one of those sub-columns, I want the table to NOT return the entire column.
You can add multiple dimensions and then by dragging them about in the properties pane you can move one above another. You can also drag things about outside of edit mode, but remember to go back to edit mode and the properties of the pivot to save your changes.
To have a column conditionally show you have to use the Show Column If function for the column.
The show column to hide if there are no negative values will be something like:
sum({<Value*={"<0"}>}Value) < 0
Which is basically saying if the total of all values less than zero is less than zero then show the column.
Where that might not be sufficient, and things get more complicated, is if you need to aggregate the values before you decide whether they are negative or not. To explain that, if in one cell you have -5, +3 and +4, that will aggregate to +2. The expression above would just look at the negatives, and arrive at -5 therefore showing the column.
To work out that the aggregated value is +2 for that cell, and check if any other cell is negative you need to do the following:
min(aggr(sum(Value), Dimension1, Dimension2)) < 0
This works out the sum of the values for every combination of the two dimensions, and then finds the lowest of those, and if that is less that zero we want to show the column. It's the aggr doing much of the heavy lifting here - so it's worth reading up on that function if you need to.
The problem you will hit with this however is that you are wanting show and hide dimensions in your pivot, so it might not always be obvious what the dimension list is.
