Total hidden values, each row (as a column)

• Type: Inserted as a column
• Description: Calculates the sum of all hidden values in each row.
• Range:
  • Columns: All columns (hidden)
  • Rows: Each row
  • Measures: First measure
• Syntax:

sum(all(h), 0, m1)

• Sample crosstab:

Example

As shown above there is a prebuilt column showing totals per row - but if you hide columns, these will not be adjusted to what you are looking at.

When you add a visibility agent, the standard behavior is to hide rows based on the assigned condition.

You can, however, also hide columns.

First select properties and visibility agents

Now pick Hide member

Choose the measure for the condition - in the case No of Sales

set the condition to value < 100 (meaning any value < 100 will fulfill this condition

And on the Action tab pick hide Year member (column)

Notice that 2022 is now hidden - because there was a value < 100 in this column (and the totals per row are not correct anymore).

Here is a perfect fit for total, column (hidden)