Hi, I have created a template for a profit and loss layout but I am having issues turning the font colour red for negative values.
Within my template i have used the format function to drive the format of the value but this turns the measure to text format which you can’t then turn red via the font colour conditional formatting.
In good old excel i would just format the cell “#,##0.00_ ;Red;”-"" does anyone have any ideas?
Hi @BCS. Instead of just using the original column (numeric) with FORMAT, keep the original column, duplicate it and put your FORMAT on the duplicate (now text), then use the original (numeric) value for conditional formatting.
Greg
Hi @Greg thanks for the reply.
The problem i have is that my measure which is utilising my template is using the switch(true() to identify my lines and then run a calculation but not all results are numbers some are percentages, my measure is a mix of numbers and percentages, extract of the measure below, I’ve not included my variables as the whole measure is quite long.
Fundamentaly everything is built off the [Actuals] measure so i tried putting that in the advanced controls conditional formatting section against this overall measure and it changes the font red for the very bottom section which directly uses the measure [Actuals] but it does not change the font for the Subtotal, Total and Total% lines and you can only add one field / measure in the advanced control conditional formatting area.
Return
IF(OrderType = “SUB TOTAL”,
FORMAT([Subtotals],
“##,##; (##,##); -”), //number format
IF(OrderType = “TOTAL”,
FORMAT(SWITCH(AccName,
“Total Revenue”,TotRev,
“Total Category1 Costs”,Category1Costs,
“Total Category2 Costs”,Category2Costs,
“Total Category3 Costs”,Category3Costs,
“Total Category4 Costs”,Category4Costs,
“Total Costs”,TotCosts,
“Category1 Margin”,Category1GM,
“Category2 Margin”,Category2GM,
“Category3 Margin”,Category3GM,
“Category4 Margin”,Category4GM,
“TOTAL GROSS MARGIN”,TotGM),
“##,##; (##,##); -”), //number format
IF(OrderType = “TOTAL%”,
FORMAT(SWITCH(AccName,
“Category1 GM%”, DIVIDE(Category1GM, Category1Rev,0),
“Category2 GM%”, DIVIDE(Category2GM, Category2Rev,0),
“Category3 GM%”, DIVIDE(Category3GM, Category3Rev,0),
“Category4 GM%”, DIVIDE(Category4GM, Category4Rev,0),
“TOTAL GM%”, DIVIDE(TotGM, TotRev,0)),
“#,###0.0%;(-#,###0.0%); -”), //percentage format
FORMAT(CALCULATE([Actuals]),
“##,##; (##,##); -”)))) //number format
Hi @BCS. I think the possibility I first mentioned would still apply; if you create a second measure that is a copy of the first, but this time don’t use the FORMAT command, everything will still be numeric. You can use your original measure in your visual (table?) and use the second measure for conditional formatting (<0 = red).
Hope this helps. If you need further assistance, to help the forum members, please attach a work-in-progress PBIX and a screenshot of the visual that’s your desired outcome marked-up as you’d like.
Greg
Just tried this out and works great!!
Thanks very much for your help @Greg I’ve been going around in circles for days trying to fix this which should be way more simple, like excel formatting, maybe one day!