Grades and other numeric measures routinely contain values with multiple decimal points. Often when creating Reports and Dashboards for presentations, certain forms of data (grades, for example) are best suited to be displayed as percentages instead of extended decimal strings.
Using the New Calculated Measure window in the Ad Hoc Editor, you can create a calculated measure to convert decimal strings to percentages. For the purposes of this case study, we will walk through converting the mdl_grades_grades.finalgrades field from decimals to percentages.
This walkthrough assumes that you have opened a new Ad Hoc View in the Ad Hoc Editor from the Grades Domain.
In the Measures panel of the Ad Hoc Editor, hover over the title bar menu:
Select Create Calculated Measure. The New Calculated Measure window opens:
In the Measure Name field, enter a name for the new calculated measure. We will name our measure "Final Grades."
In the Functions list on the Formula Builder tab, select the Concatenate function by double-clicking it. The concatenation formula appears in the Formula box.
Select the "TextFieldName" portion of the formula in the Formula box:
From the Functions list, find the Round function and double-click it to replace the selected text with the formula:
Select the "NumberFieldName" portion of the formula in the Formula box:
From the Functions list, find the IF function and double-click it to replace the selected text with the formula:
Select the "BooleanFieldName" portion of the formula in the Formula box:
From the Functions list, find the IsNull function and double-click it to replace the selected text with the formula:
Select the "FieldName" portion of the formula in the Formula box:
From the Fields and Measures list, find the Final Grade measure and double-click it to replace the selected text within the formula:
Replace the "TrueCalc" portion of the formula with the integer "0" and replace the "FalseCalc" portion of the formula with the Final Grade measure:
Replace the "Integer" portion of the formula with the integer "0" and the "text string" portion of the formula with the "%" symbol:
Finally, delete the comma and ellipses from the end of the formula. Your completed formula should appear as follows:
To ensure that your formula returns a correct value, click the Validate button. If your formula validates, you will receive a green success message:
To save the calculated measure, click the Create Measure button.
The new "Final Grades" calculated measure now appears in the measures panel, and can be added to your View in the same manner as other measures. You can identify your new measure by the unique calculated measure icon:
The following table breaks down the full formula into the 4 functions and offers an explanation of each step of the conversion:
Function 4 | Function 3 | Function 2 | Function 1 | Function 2 | Function 3 | Function 4 |
|---|---|---|---|---|---|---|
| Concatenate( | Round( | IF( | IsNull("Final Grade") | , 0, "Final Grade") | , 0) | ,'%' |
Function | Explanation | Case 1 | Case 2 |
|---|---|---|---|
| Function 1 | If the value of "Final Grade" is null, this function returns the value "TRUE" If the value of "Final Grade" is not null, return is "False" | If "Final Grade" = "92.312" Return is FALSE | If "Final Grade" = "null" Return is TRUE |
| Function 2 | If the return value of Function 1 is "TRUE," this function returns "0" If Function 1 is "FALSE," this function returns the "Final Grade" integer | If function 1 = "FALSE" Return is "92.312" | If function1 = "TRUE" Return is "0" |
| Function 3 | This function rounds the return of function 2 to the identified number of decimal points - in this case: zero | If function 2 = "92.312" Return is "92" | If function 2 = "0" |
| Function 4 | This function concatenates the return of function 3 with the identified string - in this case: the % symbol | If function 3 = "92" Return is "92%" | If function 3 = "0" Return is "0%" |