`Hide Non-Selected Values, on an Ignore Selections

[jrdunson]

Hello

I have a chart, that accumulates... totaling an amount by year

I want to allow a user to drill into the years... without the user selections to affect the totals.

So, I've gone the route of using set analysis, where I ignore year selects:   sum({$<[Year]=>}[Cash Amount])

[If I don't include this, then the totals change... adding up only for the years selected]

This works fine... however, extraneous years still show on the chart... Here is the same chart, with years 2005-2016 selected... As you can see, the non-selected years...still show on the chart... and still accumulate:

Is there a way to hide the years, not selected ...?  That is, honor user selections for which years to view , but ignore the years selected for the total amount?

Jarrell

[sunny_talwar]

Try this:

Sum({$<[Year]=>}[Cash Amount]) * Avg(1)

Here Avg(1) will become 0 forcing your expression to be 0 for years which are not selected. For years that are selected Avg(1) will be 1 and will give the unfiltered output for the expression.

[swuehl]

There are two options,

a) use advanced aggregation around your current expression, with Year as dimension

b) use an AsOf table

Both approaches are described here:

Calculating rolling n-period totals, averages or other aggregations