We help IT Professionals succeed at work.

Using Calculated Member within Crosstab to calc percentage

ShadowIT
ShadowIT asked
on
Hello Experts,

I need to calc a percentage using the following:

Vaue 1/(Value 1+ Value 2)=%
30069/(30069+10976)=73%

When I do this as a calculated member using below I get a value of 1 because it is rounding up.  As this is a currency field I need a way to properly display the percentage, any ideas?

GridValueAt(GetRowPathIndexOf("Controlled Spend ($K)"), CurrentColumnIndex, CurrentSummaryIndex)/(GridValueAt(GetRowPathIndexOf("Controlled Spend ($K)"), CurrentColumnIndex, CurrentSummaryIndex) + GridValueAt(GetRowPathIndexOf("Constrained Spend ($K)"), CurrentColumnIndex, CurrentSummaryIndex))
Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Try multiplying by 100 or formatting as a %  with 2 decimal places

mlmcc

Author

Commented:
How do I format as % when the crosstab say it must be formatted as currency?  Thanks
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
COnvert it since a percentage as a currency doesn't make sense.

I think it is cDouble

mlmcc

Author

Commented:
Like this?

CDbl ((GridValueAt(GetRowPathIndexOf("Controlled Spend ($K)"), CurrentColumnIndex, CurrentSummaryIndex)/(GridValueAt(GetRowPathIndexOf("Controlled Spend ($K)"), CurrentColumnIndex, CurrentSummaryIndex) + GridValueAt(GetRowPathIndexOf("Constrained Spend ($K)"), CurrentColumnIndex, CurrentSummaryIndex))*100))&"%"

Sorry for being so thick, doing this still won't let me add a "%"; msg box says, "A number or currency is required here...makes me remove "%".

Thanks

Author

Commented:
I just added the % sign to a text box and aligned everything.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
There should be a % on the toolbar for formatting

mlmcc