Forum Discussion
Advocate IHow 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’t want to use for conditional formatting).
- "%Var" (also not relevant for the formatting).
My goal is to color the background of the blank column based on the "VENTA" value, but considering each month individually (not in relation to the total of the matrix).
The problem is that when I try to apply conditional formatting, Power BI tends to reference the total instead of evaluating each month separately.
How can I make the conditional formatting of the blank column based only on "VENTA" and specific to each month in the matrix?
Thanks in advance!
6 Replies
- Khushidesai0109
Skilled Sharer
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 formattingSales_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
)- 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
If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
eomedesThank 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
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
- Go to the blank column where you want the formatting.
- Click on Conditional Formatting → Background Color.
- Select "Field Value" and choose Venta_Normalized.
- 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!
- Apply the measure to the blank column:
Hi eomedes
Thank you for reaching out to the Microsoft fabric community forum.
I wanted to check if you had the opportunity to review the information provided by Khushidesai0109 . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi eomedes ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi @eomedes ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
