How to format a crosstab measure based on another crosstab measure

Check list of all posts

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.

Implementation

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