Forum Discussion

darko861's avatar
darko861
Icon for Resolver II rankResolver II
5 years ago
Solved

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

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?

 

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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