Add another crosstab to the analysis: Costs and No of Sales per Customer Country (Country).
Add a calculation as a single column per Customer Country:
- Costs per Sale = sum(d1, 0, m1) / sum(d1, 0, m2)
Now we will calculate what the level of costs is in other countries compared to the American numbers – a kind of US-index.
- US index: sum(c1, 0, m1) / sum(c1, @”[North America].[United States]”, m1)
- Hide Costs, No of Sales and Costs per Sale through the Visibility formatting option.
- Change the object type to horizontal bar chart.
The end result should look like this (bar chart on the right):
Note: Even if one of the levels in a hierarchy is hidden, e.g., the Country level of a Territory/Country/State hierarchy, it should still be included in the named reference syntax.