1 of 1 people found this helpful
What if you're left just two calculated Measures -- [Current] and [Outlook] -- instead of six?
Your [Pivot field names] Dimension effectively partitions your Sales by Year,
so I suspect there would be no more need in distinct yearly Current & Outlook calculations.
With just two Measure Names on a view, there would be no empty Marks to hide.
Or I'm missing something?
Hi Yuri, yes - this is essentially what I ended up doing. I still used each of the 6 individual calculated field for the measures because they have specific business rules depending on year. However, they can be "collapsed" into the 2 measures you suggested ([Current] and [Outlook]) simply by adding the respective values together. In other words, [Current] = [2016 Current] + [2017 Current] + [2018 Current] and then allow the [Pivot field name] to partition by year. The only other trick was to ensure that the individual calculated fields only aggregated data for that specific year ([2016 Current] only summarized 2016 data and did not care about 2017 or 2018). I suppose this could have been handled by just 2 complex calculated fields, but I opted to decompose it into several more simple calculated fields. I also ended up making it more generic for the Years values (Y1, Y2, Y3, etc) so that it does not contain hardcoded year logic and will maintain accuracy as time passes.
Here's a screenshot of the finished product:
Glad it worked for you.