?
Solved

How do I write a percentage formula in BO

Posted on 2011-02-11
1
Medium Priority
?
2,938 Views
Last Modified: 2013-11-16
I created 3 measures to display a summary in vertical table:

Count of Complete
Count of Incomplete
Count of  Learners

The details are below the summary table. There is a section break on Cost Center.

My Percentage formula is:
=Percentage([Count of Complete (PTD)]/[Count of Learners (PTD)])

The results returned is "1" for every Cost Center.

My previous formula:  
=Count([Learner ID]) Where([Sub-Qual Status]="Complete")/Count([Learner ID])*100+"%"
worked correctly, but did not display 0% for nulls.

How do write the correct percentage formula that will display the correct percentage per cost center, display "%" and display "0" in the column, if null?
0
Comment
Question by:KnightKreepa
1 Comment
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 34878079
It's hard to say what might be wrong with the first formula.  There could be a problem with one or more of your "Count of" measures.  I'm not familiar with the Percentage function.  BO 6.5 doesn't seem to have that.  There's a Percentile function, but that's something else.

 As for the old formula, I'm not sure what nulls you're referring to, but if the problem is that Count([Learner ID]) is null (that _would_ keep the formula from producing a result), you may just need to add an IsNull test.  Maybe something like:

=If IsNull (Count([Learner ID])) then 0+"%" Else Count([Learner ID]) Where([Sub-Qual Status]="Complete")/Count([Learner ID])*100+"%"


 FWIW, you can also leave off the '+"%"' and let the formula produce a numeric result, and then format the cell as a percentage.  If you do that, you don't need to multiply the result by 100.  The percentage format will take care of that.

 James
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question