Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Conditional formatting with multiple column/bar values

Hi, I'm new with Power bi and i was wondering why power bi let me use conditional formatting in a normal stacked line column chart, but one I add more than one value of my tables to the chart the op...
Need Help
MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

Not really sure if this is what you need but using a disconnected table with the measure and making them part of the x-axis you are abble to have a single measure to have the condittional formatting working check the image below if this appearance looks good to you or what is missing.

 

 

Again if you can share a mockup of your data would be easier to achieve an answer.

rrhascall's avatar
rrhascall
Helper II
5 years ago

Hi Miguel,   I was reading the question and looking at your solution and this is exactly what I'm trying to do.  I have a clustered bar and line chart with Actuals and Forecast as the bars.  I would like to conditionally format the Actuals bar so that is a different color if we're still within the current month.  Looks like your solution would work.  Could you provide a few more details on making the measures part of the x-axis?  Could you provide a sample file?  Thanks!

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi rrhascall

     

    Just to have a more clear idea are your actuals and forecast in the same table?

     

    Do you have a calendar table? 

    • rrhascall's avatar
      rrhascall
      Helper II
      5 years ago

      Yes, actuals and forecast are in the same table.  I am using a calendar table but it is not marked as a date table because I'm only using monthly values (month + year) .  PowerBI won't let me mark as a true date table because of non-continguous dates

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi rrhascall ,

         

        There are several ways depending if you have unpivot or not the forecast and actuals values however in this case I'm assuming that you have the table in the following format:

        Date - Actuals - Forecast

         

        In this case create a new disconnected table with the actuals and forecast text:

         

        Now add the following two measures:

        Actuals / Forecast =
SWITCH (
    SELECTEDVALUE ( Actuals_Forecast[Values] ),
    "Actuals", SUM ( Actuals_Forecast_Values[Actuals] ),
    "Forecast", SUM ( Actuals_Forecast_Values[Forecast] )
)


Formatting Current Month =
SWITCH (
    TRUE (),
    DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), MONTH ( MAX ( 'Calendar'[Date] ) ), 1 )
        = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
        && SELECTEDVALUE ( Actuals_Forecast[Values] ) = "Actuals", "#D9B300",
    DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), MONTH ( MAX ( 'Calendar'[Date] ) ), 1 )
        = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
        && SELECTEDVALUE ( Actuals_Forecast[Values] ) = "Forecast", "#F0E199",
    SELECTEDVALUE ( Actuals_Forecast[Values] ) = "Forecast", "#A0D1FF",
    "#118DFF"
)

         

        On the Formatting current Month you need to replace the "#000000" by the codes you need for your colouring, I took the liberty of showing both actuals and forescast on different colours for current month if you want to keep the forecast with the same colour has the other months you can delete the second part of the measure:

         

            DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), MONTH ( MAX ( 'Calendar'[Date] ) ), 1 )
        = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
        && SELECTEDVALUE ( Actuals_Forecast[Values] ) = "Forecast", "#F0E199",

         

        Now use the condittional formating using the last measure: