Creating Story-Defined Thresholds (Optimized Story Experience)

When you are comparing negative values, your results may not be what you assumed they should be.

To provide more accurate results, the system uses a formula to compare measures:

if (MeasureB == 0) {       \\(Can't divide by 0)
       MeasureA/|MeasureA| > percentage    \\(absolute value of MeasureA)
   } else if (MeasureB < 0 || MeasureA < 0){
        (MeasureA - MeasureB)/|MeasureB| > percentage  \\(compare variance to percentage)
   } else {
        MeasureA/MeasureB > percentage  \\(compare ratio to percentage)
 }

The following table shows how the formula is applied.

If you put that formula into Excel, you would write it as follows: =IF(B5=0,A5/ABS(A5), IF(OR(A5<0,B5<0),((A5-B5)/ABS(B5)), A5/B5))

The following table shows the ratio and formula results from comparing the positive and negative values (profits/losses) for two years.

When you enter the value for your range, the value appears on the line at the bottom of the panel. The value includes a letter to designate whether it is thousands, millions, and so on.

You do not need to set both an upper and a lower bound if you have only one range. When you add more ranges, you can leave either the upper or lower bound empty.

1. From the Tools menu, select (Conditional Formatting).

   If necessary, expand Story Defined.

2. Select Add Threshold.

   The Thresholds panel appears.

3. Choose your model.

4. Choose a measure.

5. Choose a comparison option.

   • Number Range

   • Measure: In Comparison Measure, select the measure to compare to.

     Optional: To include a NULL data point in your results, select Set no data as zero to set the value to zero.

6. Under Ranges, set a lower bound and an upper bound for your range.

   As you type your values for the upper and lower bounds, you will see a warning appear if the value does not fall within the range. For example, a lower bound cannot be larger than the upper bound.

   (Optional) Change the icon or label for your range.

7. To add another range, select Add Range.

   Tip

   When you add ranges, the displayed icons and labels will keep cycling through the default choices: OK, Warning, Critical, Neutral, Information, Neutral, Critical, Warning, OK, and so on.

8. 1. Select Add Filters and choose a dimension from the list.

      The filter dialog appears.

      1. Select one or more members.

      2. Select OK.

9. To add another threshold, select Add Threshold.

   Note

   A warning will appear if you create a threshold range that already exists. You must either change the range or apply additional filters to the threshold.

10. When you have finished adding thresholds, click Apply.

11. Select Done.

The threshold you created will appear in the Conditional Formatting panel. It can be added to charts (see Using Thresholds in Charts) or tables (see Using Thresholds in Tables).

You can use as many or as few ranges as you like, and you can change the label names and colors. You can also change the symbols.

To change the color or symbol, select the current symbol and then select Customize Symbol.

You have five more symbols that you can choose from.

• Alert (A triangle with an exclamation mark)

• A circle around one of the following characters:

  • Checkmark

  • Error (X)

  • Outline (Donut)

  • Information (lowercase “i”)