Different colors for X-axis ( multiple years and different colors

Data Visualizations
1

hello,

attached a screen shot where I want to give each year a differen color example 2023 Green 2022 blue
2024 red.
can this for all the visuals having year as the X-axis ?
can I have a card visuals saying that this is data for december 2022 and 2023. I have not visualized
a column for 2024

any help is greatly appreciated

roger

image
image341Ă—701 12.3 KB

2

Hi @Roger,

Using DataMentor/EDNA AI built in the EDNA platform this what comes up:
Yes, using conditional formatting in Power BI to assign different colors to bars based on the year values on the x-axis is a great approach to enhance visual distinction. Here’s how you can do it:

Steps to Apply Conditional Formatting

1. Load Data

Make sure your dataset includes a column for years and another column for values.

2. Create Bar Chart

  1. Navigate to the Visualizations pane.
  2. Select Clustered Bar Chart.
  3. Drag the “Year” field to the Axis area.
  4. Drag the measure (e.g., Sales, Profit) to the Values area.

3. Apply Conditional Formatting

  1. Select the bar chart to activate it.
  2. Go to the Format pane (paint roller icon).
  3. Expand the Data colors section.
  4. Click on the fx button to apply conditional formatting.

4. Set Up Conditional Formatting

  1. In the Default color dialog, choose Format by Field value.
  2. In the Based on field dropdown, select the column that contains the year values.
  3. Define color rules by setting specific colors for different year values.

Example Configuration

Year      Sales
----      -----
2021      1000
2022      1500
2023      2000

In the Data colors section, set:

  • 2021 to blue
  • 2022 to green
  • 2023 to red

Final Step

  • Click OK to apply the changes.
  • Save the report to retain the conditional formatting settings.

By following these steps, your bar chart will effectively use conditional formatting to apply different colors to bars based on the year values, enhancing readability and visual appeal.

If you have any further questions or need additional assistance, feel free to ask!

DataMentor is great tool to use to help you with a solutions to your questions.

I hope this helps.
Keith

3

Hello,

I have created the line and clustered column chart with the color formating , but the line chart
is giving the cumulative nummber for all the years. I want 3 lines each for each year.

like the chart in excel see second screen shot

thanks in advance

Roger

image
image1551Ă—966 256 KB

image

4

HI @Roger

Using the DataMentor/EDNA AI tool, this what comes up
Yes, it is possible to have three separate lines for each year in a Line and Clustered Column Chart in Power BI. Here’s how you can achieve it:

  1. Prepare Your Data: Ensure your dataset has a column for the years and a column for the values you want to plot.
  2. Create a Line and Clustered Column Chart:
  • From the Visualizations pane, select the Line and Clustered Column Chart.
  1. Add Data to the Chart:
  • Drag the category field to the Shared Axis.
  • Drag the year field to the Legend.
  • Drag the value you want to visualize to the Line Values and Column Values.
  1. Adjust the Line Values Calculation:
  • If the line is showing cumulative values, you need to adjust the measure or the calculation.
  • Create a new measure to sum the values for each year individually without cumulative behavior. Here is an example in DAX:
LineValue = SUM('Table'[Value])
  1. Format the Line Chart:
  • Go to the Format pane.
  • Under Shape, ensure the lines are set to represent individual years in the legend.

This setup will allow you to have separate lines for each year in your Line and Clustered Column Chart.

I’m not sure this if power bi can really do that but you might have to look at a deneb solution.

DataMentor/EDNA AI tools can help you will your issues.

thanks
Keith

5

Keith

I don’t see the option :
“Drag the category field to the Shared Axis” when I select the visual
line and clustered column chart

Roger

7

Hi @Roger - Don’t think Power BI line and bar chart supports this as of now. What you can do is to create measures instead and put it in the lines y-axis.

2021 line = CALCULATE( SUM( Table[Values] ) , Table[year] = 2021)
2022 line = CALCULATE( SUM( Table[Values] ) , Table[year] = 2022)

image

Not an ideal solution but a workaround of sort.

Thanks
Ankit J

1 Like
8

Another option would create something in Deneb.

9

Ankit,

thanks for the tip, I will use it in my report,
weard that this functionality is not available in power BI where it is standard in excel

kind regards

Roger

10

Hi @Roger - Do try it out and if working fine, then please mark this post as closed.

Thanks
Ankit Jain