Create KPI based on Expressions

In this example, we create a KPI that shows gross margin in %. This KPI will be based on the expressions INCOME and COGS.

  1. In the Report Designer menu, select KPIs and then Manage KPIs.

  2. In the dialog that opens, click New.

  3. In the next dialog, enter a KPI group name (in the example Financial KPIs). You can also add a description.

  4. Click Add and enter a name of the KPI; GROSS MARGIN %. You may also enter a comment.

  5. This KPI will be based on expressions, so make sure that the Based on Expressions option is selected.

    Next, you must indicate which measure the expressions that will be included in the KPI are based on.

  6. Click the Edit measure button in the Measure box.

  7. Find and select the measure that the expressions INCOME and COGS are based on. In our case, this is Net Amount (under General Ledger Transactions).

  8. Click OK to confirm.

  9. Click the Edit button in the Formula field.

    The KPI Editor opens:

    Name
    1 Expressions
    2 Formula area
    3 Operators area

    The KPI GROSS MARGIN % will consist of the expression INCOME minus the expression COGS divided by INCOME to get the result in percent.

    The formula will look as shown in the figure below:

  10. To build the formula, perform the following steps:

    a. In the Operators section, click ( (left parenthesis) twice.

    b. Drag the expression INCOME into the Formula area*.*

    c. In the Operators area, click * (multiply by) and type -1. This is to convert the income amounts that are negative in the ERP system into positives so that the entities that will be calculated are both positive in order to give the correct result.

    d. In the Operators area, click ) (right parenthesis).

    e. In the Operators area, click (minus).

    f. Drag the expression COGS into the Formula area*.*

    g. In the Operators area, click ) (right parenthesis).

    h. In the operation section, click / (divide by).

    i. Drag the expression INCOME into the Formula area.

    j. In the Operators area, click * (multiply by) and type -1. (Again to convert the income amounts into positives.)

    k. In the Operator section, click ) (right parenthesis).

    l. Click OK to confirm.

  11. Click OK and then Close to exit the KPI dialog.

Last modified August 7, 2024