Format Cells for Values Above Average

To format values in a table that are above (or below) average, follow these steps:

1. 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.

2. Click the Conditional Formatting button.

3. In the Conditional Formatting window:

   • Set Set For to Total Sales Amount.

   • Keep Based On as Field Value.

   • Open the Style dropdown and select New Rule.

4. In the New Formatting Rule window that opens, select Format only values that are above or below average.

5. Next to the Preview area, click the Format... button.

   This opens a formatting window where you can:

   • Change the font style or color,

   • Add a fill color,

   • Modify borders, or

   • Adjust the number format.

6. Once you’ve set your desired formatting style, click OK in the formatting window, and then click OK again in both the New Rule and Conditional Formatting windows to apply your settings.

Example Query (Adventure Works DW)

You can use the following native query to create a dataset suitable for this type of conditional formatting:

SELECT TOP 20
    p.EnglishProductName AS ProductName,
    c.EnglishProductCategoryName AS Category,
    SUM(f.SalesAmount) AS TotalSalesAmount,
    SUM(f.OrderQuantity) AS TotalQuantity,
    SUM(f.SalesAmount) / NULLIF(SUM(f.OrderQuantity), 0) AS AvgSalePerUnit
FROM
    FactInternetSales f
JOIN
    DimProduct p ON f.ProductKey = p.ProductKey
JOIN
    DimProductSubcategory s ON p.ProductSubcategoryKey = s.ProductSubcategoryKey
JOIN
    DimProductCategory c ON s.ProductCategoryKey = c.ProductCategoryKey
JOIN
    DimDate d ON f.OrderDateKey = d.DateKey
WHERE
    d.CalendarYear = 2013
GROUP BY
    p.EnglishProductName,
    c.EnglishProductCategoryName
ORDER BY
    TotalSalesAmount DESC;

This is the table with the conditional formatting applied: