Conditional colouring on bar number in visualization

[Anonymous]

Hello fellow Power BI-ers,

My experience with Power BI is still limited.
Right now I'm trying to set up some colours for the bars in a bar graph.

I've got a large dataset, over multiple years. I'm showing the top 5 of costs per customer.

Now let's say my client has 100 customers. There is a slicer for year in place. Whenever we move the slicer, the top 5 changes.

Every year the amount of customers, and the actual customers themselves change. (Names, row number, etc.)

What I'm trying to do is set the colour of the bar not dependent on any value of the customer, but of the position of the bar.
So imagine 6 bars next to eachother. The first bar is black, the second bar is green and the third bar is blue. The fourth bar is black again, the fifth green, and the sixth blue.

I cannot find a way to achieve this. Does anyone know if there is a way to achieve this? And if so, how?

Yours sincerely,
Sorrol

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[Anonymous]

@v-diye-msft If this is still necessary after the screenshots I provided, I'll do so with the sample data. Thanks for the advice!

[parry2k]

@Anonymous did you looked at conditional formatting, you can create a measure for colors and use that in conditional formatting

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Hello Parry2k,

I did take a look at those, but they base the colour on the value in a row/column.
What I'm looking for is a colour for a bar number, regardless of the value of the y-axis or the value of the x-axis.

So if I make a bar chart with on the x-axis years, and on the y-axis a count of invoices, regardless of the result, the leftmost bar needs to be black, the second green, and the third blue, etc.

So, if I filter 2015-2019, 2015 would be black, 2016 would be green, and 2017 would be blue. 2018 would be black, and 2019 would be green again.

Then if I change the filter to 2014-2019, 2014 would be black, 2015 would be green and 2016 would be blue. etc.

I've not found a way to achieve this through the conditional formatting you linked.

[amitchandak]

@Anonymous , I did not try this. But have a measure like this

Color Date =
var _min =minx(allselected(Date,Date[Year])
return
Switch( true(),
FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen",
FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =1 ,"blue",

///// Add more
"red")

The idea is to take diff from min selected year and use color grouping on these number

Use this in conditional formatting after choosing "field" option

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column

[Anonymous]

@amitchandak 

I like this idea. Sadly it only pertains to years.
What I'm looking for is a solution that works for any kind of label. With any amount of bars.

But it's definitely a great start!

[parry2k]

@Anonymous I guess you mean data label, it is already good idea to share screenshots, there is nothing called bar labels. If you are referring to data labels conditional formatting, this feature it is not available. Please create an idea on ideas forum or vote if idea already exists.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@parry2k I don't remember mentioning bar labels, but that doesn't matter.

Due to NDA I cannot show screenshots. Instead I made some example data and will show screenshots of that. I'll show current state, and desired state.

Current state unfiltered:

Current state filtered:

Desired state unfiltered:

Desired state filtered:

As you can see, the data label should not define the colour. The position of the bar does.

Is this achievable in Power BI?