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]
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