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.
Comments
Hi Ole.
Is it possible to make a direct reference as well you if add another dimension to the crosstab that is not part of the existing hierarchy? For example customer - so the column order would be: Territory (dimension 1, level 1) - Country (dimension 1, level 2) - Customer (dimension 2).
/Thomas
Hi Thomas,
Yes. The syntax just needs a little adjustment when it is not a "real" hierarchy. I usually call it a "combined" or a "custom" hierarchy.
I have recreated the example as outlined by you: Territory and Country from a real hierarchy on the first two levels, and then Company as a custom, third level of the hierarchy.
As an example, to get the value for Casual Clothing Retail in Malaysia, Asia, the syntax would need to be:
sum(d1, @"[Asia].[Malaysia]";"[Casual Clothing Retail]", m1)
Notice the semicolon and placement of quotation marks.
BR / Ole
Hi Ole.
The solution above works perfectly - thank you. I have another example i hope you can help with :-)
I have a crosstab where i have amounts per kg for each month in the period 2016-2021. I want to add a calculated column where i calculate the average amount per kg in 2020 which i consider my baseline.
Can i use a direct reference here as well to calculate the average only for rows where the year = 2020 (the second column)? The "Year-Month" and "Year" columns are not part of a hierarchy. I have tried different solutions but keep getting a syntax error.
/Thomas
Hi Ole,
is it also possible to set a named reference to a user dimension member value?
BR
Bernd
Please sign in to leave a comment.