Calculations in a Pivot table

[#1]

Hi,

Please could you help me with the following?

I am busy setting up the above Pivot table.  I've got 2 dimensions running at the top which is CheckInDate and UtilisationStatus and then another dimension along the side which is Region.  My expression is Nr of Days.  I want to setup a Measure called "Utilization".  This will be the Nr of Days Confirmed/Total Nr of Days

1. The first question is what should my expression look like?  At the moment I am using

(UtilizationStatus = 'Confirmed', sum({$<Year ={">=$(vYear)"}>} Days)/sum(Days))

This only gives me an answer of 100% for the Gauteng region.  The other regions do not have any answer.

2. The second question is How do I get the Utilization measure not to show under the "Available" and "Confirmed" column? I only want it to show once which is after the "Total" column.

Thanking you in advance for any help.

Antoinette

[tcullinane · Accepted solution]

If you name the labels for the expressions you could use the expression labels from your table:

Confirmed / Total

Other wise an expression such as:

sum({<UtilizationStatus={'Confirmed'}, Year={'>=$(vYear)'}>}Days) / sum({<Year={'>=$(vYear)'}>}Days)

[#3]

Wonderful!  Thank you so much!

How do I get the measure not to show under the "Available" and "Confirmed" columns?

Thanks

[#4]

Hi,

No worries.  I've made everything measures and now Utilization is only showing once.  Thanks

again for the help.

antoinette