highlighting results in table going over limit

[Anonymous]

Hello

I have a regular report with a table showing seller's results in Power BI.

The distribution channel imposed different limits on various product lines which should not be exceeded and I want my report to check the results against these limits and highlight seller entries where the limit is breached so it can be quickly referred for inevstigation and corrected as needed.

I wanto to build this in a way that will continue over time as the limits can be changed each year.

a) do I need  to create a combo of product&year in sales table against my limit reference table

b) how would i create a measure to check the limit for each product and confrim if this is exceeded or not

b) is there an easy way to build the above measure into a conditional formatting to be used in the sales reporting table or is there a better way to implement this?

Would appreciate any views or suggestions

[mh2587]

Here are few suggestions for your scenario, what I got in my mind.

• Separate Limits Table: Create a standalone Product Limits table with Product, Year, and Limit columns to easily manage limit changes over time.
• Use Relationships: Link the Sales and Limits tables using Product and Year instead of calculated columns to keep the model cleaner.
• Enhanced Limit Measure: Create a more informative measure (e.g., "Exceeded", "Within Limit", or "No Limit") to display meaningful results.
• Conditional Formatting: Use color scales, icon sets, or traffic lights to visually highlight breaches in the report.
• Filter for Breaches: Add a slicer to quickly toggle between all results and only those where limits are exceeded.
• DataActivator: You can also use DataActivator for alert notication e.g in your case apply condition when exceed, send message to team or email to conerned person.

[Anonymous]

Thank you for your reply

I already have the limit table set up as described and have now connected it to my 'sales' table as suggested.

Could I please get some help with the DAX formula for my example:

product A, B, C

limit 

product A in 2024 1m, 2025 1m

product B in 2024 2m, 2025 3m

product C in 2024 1m, 2025 1.5m

(-the limits are imposed for each sale so should check each entry and not accumulation

- I currently have about 30 different products so not sure if I need to specify each one of them or can write a DAX formula to looks for an exact match in the limit table)

Thank you for the remaining suggestions for filter and data activator, very useful!

[mh2587 · Accepted solution]

ColorCode = 
VAR SalesAmount = Sales[SalesAmount]
VAR ProductLimit = 
    CALCULATE(
        MAX(Limits[Limit]), 
        FILTER(Limits, 
            Limits[Product] = Sales[Product] && 
            Limits[Year] = YEAR(Sales[Date])
        )
    )
RETURN
    IF(
        ISBLANK(ProductLimit),
        BLANK(),                             -- No limit set (no color if blank)
        IF(SalesAmount > ProductLimit, 1, 0) -- 1 for exceeded (red), 0 for within limit (green)
    )
//Use 1 and 0 for color combination in conditional formatting

[moncx]

Hey,

if you have limit table, it is connected to sales and you have a table that shows values per product then you can use a measure like this to set a color:
Change Color =
VAR _value = SUM('YourTable'[Sales]) --or put measure/calculation on how you calculate sales
VAR _limit = SUM('LimitTable'[Value])
RETURN
IF (
_value<_limit, 
"#6dae89", -- Green Color 
"#ed999e" -- Red Color
)

And then later put this measure into conditional formating for needed column (conditional formating based on field value, examples ho to do it: Apply conditional table formatting in Power BI - Power BI | Microsoft Learn ).

Hope it helps!

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.