Calculation combined with visibility

Here we will work with calculations as a new measure and referencing a certain dimension value.

• Create a new Cost analysis with a crosstab showing Costs and No of Sales per Salesperson and by Product Hierarchy(Product group).
  
  
• Add global criteria Time = 2019.

• Add a calculation (as a new measure):

Costs per Sale = sum(0, 0, m1) / sum(0, 0, m2)

We have now added a new measure which can now be referred to if needed (in this case as m3).

Notice that when using calculation as a new measure you normally do relative referencing – we relate to the current column and row.

Now, the crosstab looks like this:

• Use visibility agents to hide all salespersons who had a total No of Sales less then 100 during the period.
  

The condition for the visibility agent looks like this:

Use the Visibility formatting to hide the Underwear and T-shirt numbers.

• Go to Properties and Visibility and choose hide a range of Product Hierarchy.

• Choose Position 3 from the first – and Define range end – choose Position 1 From the last – click

This will hide the last two columns of the cross tab and the resulting cross tab should now look something like this:

Hide No of Sales and Costs using the Visibility formatting and change object type to Column chart.

With a little further formatting, the result should look something like this: