Forum Discussion
Conditional formatting Min and Max value within each month in a Clustered Column Chart
Community ChampionJackieC even with DAX, the conditional formatting for your bar chart isn't possible with something in the 'legend' field, so you'd have to create a separate measure for each M1, M4, etc and then it would be rather complex DAX.
Can you send screenshots of what your 'build a visual' pane looks like for the table and column chart? We may be able to provide some tips for you, but I'm not quite sure how you've set up your data model and think it may be a bit tricky. I'm sensing there may be an unpivot requirement as part of this....
AllisonKennedyThanks, pls see screenshots below on what I have
Table view
Build visual for the colorful chartBuild visual for the simple table below
- AllisonKennedy
JackieC It may just be because I love DAX so much, but I can't think of a solution without DAX for this one;
I would recommend unpivoting all the columns you're currently using in the 'y-axis' (both the column and line) - you can do this in Transform Data (Power Query).
Then create a measure for [Forecast] and put it in the column y-axis and a measure for [Actual Usage] and put it in the line y-axis
Then you can put the newly created 'Attribute' column (it will be created when you do the unpivot) in the x-axis and apply conditional formatting on the y-axis column colour.
DAX for measures:
Forecast =
SUMX(
FILTER( tablename, CONTAINSSTRING( tablename[Attribute], "forecast") ),
tablename[value])
Actual Usage =
SUMX(
FILTER( tablename, CONTAINSSTRING( tablename[Attribute], "actual") ),
tablename[value])
