KPI: indicadores claves de desempeño en Power BI

"Tell me how you measure me and I will tell you how I work". "What is not measured, cannot be improved".

From the English acronym Key Performance Indicators or key performance indicators, also known as key performance indicators, are graphic elements (icons with colors or shapes similar to those used in Excel conditional formatting) that reflect the quantification of values (whether financial, production, HR department, distribution areas...).
They are used to reflect the performance of a department, a plant for example, or even an entire organization so that visually and quickly, information can be collected by the user and concrete conclusions can be drawn.

The objectives of a company or department are set based on known indicators (ours or from other companies in the same or similar activity).

KPI's direct the user's attention to ask questions, not to provide answers. They are attention signals.

Depending on the shape or colors of these indicators, the questions to be asked can be:

At what point in production is the failure?
Does the operator fail?
Does the machine fail?
Is the quality of the raw material not adequate?...

To define those values as KPI, we have a panel where we will regulate the thresholds of value that define the different icons of the indicator as well as the range of values for which we want to define different indicators by colors and shapes.

When we generate a Pivot Table from the data in that table, we can include that KPI in the Values area, presenting it in the Pivot Table in the data area, not as numerical values but as much more significant and visual graphic indicators. At all times and from the data model, we can edit the configuration of the KPI's to tabulate the thresholds and cutoff values for the different indicators.

Progress through a practical case

Based on the data model we are working on from the related tables for an order management process (Neptune database), we need to quickly know by regions of the United States the total sales made, highlighting in green, red or yellow those that are within or outside the set sales targets for that country. How are sales going to contact the area distributors? Do we need to take any corrective action?

To meet this need, the information that we must present in a Pivot Table will be on one hand the Country, Region, perhaps City, as well as the sums of the totals of the detail lines of all orders placed by all customers in THAT country (in this example, the United States).

The tables involved in this process are therefore the ORDERS table (because it contains the records corresponding to all orders placed and which customer placed them), the CUSTOMERS table because it is where we have the customer data and therefore their country, region, and city), the ORDER DETAILS table (because it contains the units or quantity ordered and which items have been ordered) and the PRODUCTS table because it contains the selling prices of the items...

In summary, the intervening tables are: ORDERS, CUSTOMERS, ORDER DETAILS, PRODUCTS.
This would be the Diagram View that presents a more global vision of the "ingredients" we need:

From all these tables, we choose for example the Order Details table as the table in which to place the information we need and which we will later transfer to a Pivot Table.

The columns we insert in the table are the following:

- Total order:

=[Quantity]*related(Products[UnitPrice])-([Quantity]*related(Products[UnitPrice])*[Discount])

- Customer:

=related(Orders[CustomerId]) (not necessary in this approach)

- Country of the Order:

=related(Customers[Country])

- Region of the Order:

=related(Customers[Region])

- City of the Order:

=related(Customers[City])

And the table of the Order Details sheet looks like this after summarizing with the sum function the total of the orders in the totals area:

As in our example we want to represent the total orders (field Total order) in the form of KPI's, we select the Sum Total order field in the Calculation Area and click on the Create KPI button in the Calculations button group. Once the KPI is associated, we can Edit its configuration from the context menu of that cell or Delete it.

In our case, we want to associate color icons red, green or yellow to the sum of sales that fall within certain value intervals:

• Sales below ?10,000 will be displayed in red (take measures).
• Sales between ?10,000 and ?30,000 in yellow (monitor closely).
• Sales over ?30,000 in green (everything is fine).

In the KPI configuration panel, we adjust the thresholds for each of the indicators.

The KPI Base Field (value) is the name we have assigned to the summary field for which we want to define the KPI.

In the KPI Status section, we have to define the value that will be set as the "maximum limit" to regulate the value intervals for the different graphic indicators. This target value can be defined as:

1. Percentage relative to the value of ANOTHER calculated summary field that we have on our sheet, or...
2. Absolute value, which we will have to type.

In the thermometer scale that appears in the central area of the dialog box, we will define the status thresholds, color change for the indicator. From the 4 scale models that appear below to the right, we can reverse the colors (red for smaller values and green for larger values or vice versa), and we have scales of 3 intervals or 5 intervals.
At the bottom, we can choose the icon style with which the values of this KPI will be represented (there are 10 models to choose from):

In the lower left section of Descriptions change the panel and in this part we can write, by typing, some textual values for KPI description, value description, status description and destination description. This written information as comments will provide greater clarity to the KPI configuration panel.

At this point, we have prepared the table to insert a Pivot Table in the Excel sheet of our workbook, for example in a New worksheet.

Having defined a KPI, in the field list of the pivot table it will appear as an element of the corresponding table, in this case Order Details, the totals element called Sum Total order with a traffic light icon to its left. If we expand it in the field list, we can add to the VALUES area of the pivot table, the numeric Values, the Target value or the Status which are the KPI icons (see the following image):

Next, we will choose from the customer table the Hierarchy field (previously defined from the Data Model Diagram view) that we have called Location (in this case) as the title of ROWS.
In the VALUES section, we will include the KPI's Sum Total order and Status Sum Total order.

Finally, we would Filter the pivot table to only show the values for the United States... This can be done in two ways:

• Filtering from the drop-down controller of Row Labels in the pivot table itself...
• Inserting a Slicer by Countries in the sheet... (more graphical and easier to handle).

Finally, the desired result appears on the screen. From the slicer board, we select United States and from the context menu of any row label, we choose to expand all hierarchy levels to City:

To modify the KPI thresholds from the Excel Sheet with the pivot table, using the KPI's button in the Power Pivot sheet, we can define a New one or Manage the ones defined in the data model.