how do i add a kpi

Question

how do i add a kpi

Kenneth Borruso 0

I have a power BI I am building a KPI I need to know how to sum the sum of all values of a field like total sales where each order has a total sales. I need total sales of total sales.

0 comments

2 answers

Your answer

Answer 1

Hi Kenneth,

Thank you for sharing your concern.

If each order appears only once in your table and the Total Sales field contains the sales amount for that order, you can create the following DAX measure:

Total Sales KPI = SUM('TableName'[Total Sales])

Replace TableName with the actual name of your table. You can then add this measure to a Card visual or use it as the Indicator value in your KPI visual.

However, if the same order appears across multiple rows and its Total Sales value is repeated on each row, using SUM() directly may count the same order more than once. In that case, you can create a measure similar to the following:

Total Sales KPI =
SUMX(
 VALUES('TableName'[Order ID]),
 CALCULATE(MAX('TableName'[Total Sales]))
)

This measure calculates the Total Sales value once for each unique Order ID.

For detailed guidance, you may find these articles helpful:

In case you require more in-depth guidance or support with these steps, I’d recommend posting your query in the Microsoft Fabric Community – Power BI forums. This is a dedicated channel for Power BI discussions. The reason I suggest posting a new topic there is that resources and expertise on Power BI are limited in the general Q&A forum. In the Microsoft Fabric Community, you’ll find the most qualified group of respondents, and other partners who regularly read the forums can share their knowledge or learn from your interaction.

User's image

I hope this information is helpful. Should you have any further questions or need additional assistance, please feel free to share them in the comments below. I will be happy to help.

If the answer is helpful, please click "Yes" and kindly upvote it.

Note: Follow the steps in the forum documentation to enable email notifications if you want to receive email notifications related to this topic.

Answer 2

Create a Total Sales measure that sums the order-total column, and then use that measure as the KPI’s indicator.

1. Create the Total Sales measure

In Power BI Desktop:

In the Data or Report view, right-click your sales table in the Fields/Data pane.
Select New measure.
Enter this formula, replacing the table and column names with yours:

Total Sales =
SUM ( Sales[Order Total] )

SUM adds every numeric value in the specified column.^1^ Measures also recalculate according to the report’s filters and slicers.^2^

This is correct when each row represents one order, for example:

Order ID	Order Total
1001	100
1002	250
1003	75

The measure returns 425.

If an order appears on multiple rows

If the same complete order total is repeated on every product line, a direct SUM would count that order more than once. Sum one value per order instead:

Total Sales =
SUMX (
    VALUES ( Sales[Order ID] ),
    CALCULATE ( MAX ( Sales[Order Total] ) )
)

This creates a distinct list of order IDs, takes one order-total value for each order, and adds those values. SUMX evaluates an expression for every row of a table and then returns the sum.^3^

Do not use that pattern if Order Total is actually the value of each individual product line. For line-level data, use:

Total Sales =
SUM ( Sales[Line Total] )

If line total must be calculated from quantity and price, use:

Total Sales =
SUMX (
    Sales,
    Sales[Quantity] * Sales[Unit Price]
)

2. Add it to a visual

For a simple number without a goal:

Select the Card visual.
Drag the new Total Sales measure into the card.
In Format visual, set the currency, decimal places, and display units.

For a KPI that compares sales with a target:

Add Total Sales to a temporary chart as the value.
Add a date field, such as Calendar[Month], as the trend axis.
Sort the date axis in ascending chronological order before converting the chart; KPI visuals do not provide a sorting option after conversion.
Select the KPI icon in the Visualizations pane.
Assign:
- Indicator/Value: Total Sales
- Trend axis: your date or month field
- Target: a sales-target measure or field
Open Format visual to configure currency, target label, icons, and whether higher values are better.^4^

A KPI is intended to evaluate a measure against a defined target. If no target or trend is available, a Card is usually the appropriate visual for displaying total sales.

References