Forum Discussion

eomedes's avatar
eomedes
Icon for Advocate I rankAdvocate I
1 year ago

How to apply Conditional Formatting to a column in a matrix based on "VENTA" per month

Hi, I have a matrix in Power BI with the following columns:   A blank column (where I want to apply conditional formatting). "VENTA" (sales value per month). "% of total" (which I don...
Need Help
Tips and Tricks
Khushidesai0109's avatar
Khushidesai0109
Icon for Skilled Sharer rankSkilled Sharer
1 year ago

Hiii eomedes 

 

Create a measure to normalize "VENTA" per month

Sales_Per_Month =
VAR CurrentMonth = SELECTEDVALUE(YourDateTable[Month])
RETURN
CALCULATE(SUM(SalesTable[VENTA]), ALLEXCEPT(SalesTable, YourDateTable[Month]))


Create a measure for conditional formatting
Sales_Formatting = 
VAR MaxVenta = MAXX(ALLSELECTED(SalesTable), SalesTable[VENTA])
VAR MinVenta = MINX(ALLSELECTED(SalesTable), SalesTable[VENTA])
VAR CurrentVenta = SUM(SalesTable[VENTA])
RETURN
IF(
CurrentVenta >= MaxVenta * 0.8, 1, // High sales (80% or more of max)
IF(CurrentVenta <= MinVenta * 1.2, -1, // Low sales (close to min)
0) // Neutral
)

  1. Apply the measure to the blank column:
    • Go to Conditional Formatting → Background Color.
    • Choose Rules or Field Value.
    • Select Sales_Formatting as the field.
    • Assign colors:
      • 1 (High Sales) → Green
      • 0 (Neutral) → Yellow
      • -1 (Low Sales) → Red
Now, each month's sales will have its own conditional formatting based on "VENTA" independently.


If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
 



eomedes's avatar
eomedes
Icon for Advocate I rankAdvocate I
1 year ago

Thank you very much for your help!

However, I need it to be in a gradient. If that's not possible, I would like to have more than 5 color ranges to create a semi-gradient effect.

  • Khushidesai0109's avatar
    Khushidesai0109
    Icon for Skilled Sharer rankSkilled Sharer
    1 year ago

    This measure scales "VENTA" values from 0 to 1, ensuring each month is evaluated independently.

    Venta_Normalized =
    VAR CurrentVenta = SUM(SalesTable[VENTA])
    VAR MinVenta = CALCULATE(MIN(SalesTable[VENTA]), ALLEXCEPT(SalesTable, YourDateTable[Month]))
    VAR MaxVenta = CALCULATE(MAX(SalesTable[VENTA]), ALLEXCEPT(SalesTable, YourDateTable[Month]))

    RETURN
    IF(
    MaxVenta = MinVenta,
    0.5, // To avoid division by zero, set mid-scale if all values are the same
    (CurrentVenta - MinVenta) / (MaxVenta - MinVenta)
    )

    pply the Measure for Gradient Formatting

    1. Go to the blank column where you want the formatting.
    2. Click on Conditional Formatting → Background Color.
    3. Select "Field Value" and choose Venta_Normalized.
    4. Set a gradient color scale, for example:
      • 0 (lowest sales) → Red
      • 0.5 (mid sales) → Yellow
      • 1 (highest sales) → Green

     

    If this helps, I would appreciate your KUDOS!
    Did I answer your question? Mark my post as a solution!