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
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: