Building a P&L in Power BI (conditional formatting)

[darko861]

Hi Community,

I have been watching Matt Allington's video on how to build a P&L in Power BI. This is very informative.

Video

https://exceleratorbi.com.au/building-a-pl-in-power-bi-part-2-percentages/

The Matrix he created has one Measure in the Values section of the matrix:

Total Amount CalcType = VAR CalcType = SELECTEDVALUE(Header[CalcType]) // should it be a simple total or a running total?

VAR DisplayDetailCode = SELECTEDVALUE(Header[Detail]) // 1 if the detail should display and 0 if detail should be hidden

VAR isSubHeaderVisible = ISFILTERED(DimAccounts[Subheader]) //we need to know if the sub header is trying to show itself

VAR Result = SWITCH(TRUE(),

isSubHeaderVisible=TRUE() && DisplayDetailCode = 0 ,BLANK(),

CalcType=1 ,[Magic Additive Total],

CalcType=2 ,[Running Total],

CalcType=3, FORMAT([% of Additive Total],"0.0%"),

CalcType=4,FORMAT([% of Running Total],"0.0%")

)

RETURN Result

If you were to use conditional formatting on his matrix with different color combinations everything works fine, but the section that has percentages cannot be formatted with any color, due to it being read as text and not a number. Is there a way to add some color to the percentages with some Hex code by tweaking his syntax? E.g if the percentage is negative add red color to it, or if positive then add green color?

[amitchandak · Accepted solution]

@darko861 , Create one additional measure without this format, then you can create a conditional formatting based on that.

You can also create a color measure, where you can return color based on CalcType and measure value and use that in conditional formatting using the filed value option

example

color =
switch ( true(),
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity1" && sum('Table'[Value]) >500,"lightgreen",
FIRSTNONBLANK('Table'[commodity],"NA") ="commodity2" && sum('Table'[Value]) >1000,"lightgreen",
// Add more conditions
"red"
)

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[darko861]

Thank you!