Direct reference to dimension member value

Add another crosstab to the analysis: Costs and No of Sales per Customer Country (Country).

mceclip0.png

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):

mceclip15.png

Was this article helpful?
0 out of 0 found this helpful

Comments

3 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

    0
  • 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

    0
  • 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

    0

Please sign in to leave a comment.