Saturday, August 3, 2013

How to format a crosstab measure based on another crosstab measure

Requirement and analysis

Create a crosstab below and mark colors as green, yellow and red based on target

This is a typical requirement to apply conditional style based on another measure. The ex post (How to make bar chart conditional palette based on another measure ) has demonstrated the similar idea. Please note that
1.  We can’t add another measure as property at crosstab node member to resolve this problem, as target in this case is based on target measure at cell level.
2.  We need to hide another measure and measure row in this case. We can’t use box type “NONE”, as it will break crosstab layout. The solution is to HIDE.
Please see attached report specification (version 10.2) for detail reference.

1. Create a query using GOsales (Query)

Region: [Sales (query)].[Retailers].[Region]
Product line:[Sales (query)].[Products].[Product line]
Revenue:[Sales (query)].[Sales].[Revenue]
Planned revenue:[Sales (query)].[Sales].[Planned revenue]
Target: ([Revenue]-[Planned revenue])/[Planned revenue]

2. Create a new crosstab report

3. Add conditional style on target
Apply the existing condition style for revenue, and then report will look as below

3. Hide Target and measure row by following actions
Unlock Target measure title, and then remove
Highlight target% crosstab intersection, and define content yes
Then select both Target% measure and intersection, define padding as 0

Then select measure row and define box type as NONE


  1. Could you please explain how to apply conditional style created for Target % to the Revenue measure? Does the last section intended to perform the application of conditional style created for Target% on the Revenue measure? I am confused. Please help.

  2. Amazing Post, it solved my problem, I like the idea behind embedding images in your documentation, extremely helpful. you are the best.

  3. This is a very helpful post, only issue I find is it works to HTML or PDF but not Excel, any hints for Excel output ?