Format Cells With an Icon Set

In this guide, we’ll walk through the steps for applying an icon set to a table column, and then we’ll finish with a practical example using the AdventureWorks Data Warehouse.

Select the table on your report or dashboard.
Once the table is selected, the action buttons appear on the right-hand side of the designer.
Click the Conditional Formatting button.
In the Conditional Formatting window:
- Set Set For to the column you wish to apply the format to.
  In this example, set it to Profit Margin.
- Leave Based On set to Field Value.
Open the Style dropdown and select Icon Sets.
Next to the Icon Sets style, click the pencil icon to view or edit the rule properties.
By default, the selected icon set (green upward arrow, yellow sideways arrow, red downward arrow) follows these rules:
- Green when the value is ≥ 67
- Yellow when the value is < 67
- Red when the value is < 33
These default thresholds generally work well, but users can customize them if desired.
Click OK in the rule properties window, and then click OK again in the Conditional Formatting window to apply your settings.

Example Query (Adventure Works DW)

The following native query can be used to display product performance data suitable for this conditional formatting example:

SELECT TOP 20
    p.EnglishProductName AS ProductName,
    SUM(f.SalesAmount) AS TotalSalesAmount,
    SUM(f.TotalProductCost) AS TotalCost,
    SUM(f.SalesAmount) - SUM(f.TotalProductCost) AS Profit,
    ( (SUM(f.SalesAmount) - SUM(f.TotalProductCost)) / NULLIF(SUM(f.SalesAmount), 0) ) AS ProfitMargin
FROM
    FactInternetSales f
JOIN
    DimProduct p ON f.ProductKey = p.ProductKey
JOIN
    DimDate d ON f.OrderDateKey = d.DateKey
WHERE
    d.CalendarYear = 2013
GROUP BY
    p.EnglishProductName
ORDER BY
    TotalSalesAmount DESC;

This is the table with the applied conditional formatting: