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.

mceclip0.png

  • Add a calculation (as a new measure):

mceclip1.png

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:

mceclip2.pngmceclip3.png

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

mceclip4.png

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.

mceclip10.png

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

mceclip8.png

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

mceclip11.png

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

mceclip12.png

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

mceclip13.pngmceclip14.png

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

Comments

0 comments

Please sign in to leave a comment.