cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

I have a scatter chart (Bubble Chart) about the Score vs Speed on each date where:
Details: Date
Legend: Date
X-Axis: Score
Y-Axis: Speed

Size: Total Questions

 

My question is, can I set my bubble color as the earliest date has lightest color and latest date has darkest color?

Jamie_wong_0-1572409921082.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

Re: Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

Hi,

This is not directly possible but there is a workaround. Please follow the steps given below:

You will have to create 2 columns. (You can do this in measure also by summarizing but to show demo i have implemented using 2 columns)

Column 1 Index:

Go to Edit Queries -> Sort the date column you are going to use -> Click on Add Column tab -> Index Column and create column starting from 1 with increamenting value as 1. 

 

Column 2 Calculation:

Create column 2 with the following calculation:

Column =
IF(
'Time'[Date] = MINX('Time', 'Time'[Date]),
0,
IF(
'Time'[Date] = MAXX('Time', 'Time'[Date]), 1,
DIVIDE(1, 'Time'[Index.1],BLANK())
)
)

Once this is done then go to the visualizations pane -> format -> data colors -> click on the white box under Default color. Then select the following options:
 
diverging (1).png

 

This worked fine for me on my sample data.

 

Thanks,

Vinay Dandwani

View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
Memorable Member

Re: Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

@Jamie_wong 

I have tried to re-pro the issue from my end with scatter chart and seeing that there is no option in Format.(Back Ground Color and Data Color)

 

Please post your idea in "Power BI Ideas Forum Page".

 

If this post helps, then please consider Accept it as the solution to help the other members find it more

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Highlighted
Advocate I
Advocate I

Re: Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

Hi,

This is not directly possible but there is a workaround. Please follow the steps given below:

You will have to create 2 columns. (You can do this in measure also by summarizing but to show demo i have implemented using 2 columns)

Column 1 Index:

Go to Edit Queries -> Sort the date column you are going to use -> Click on Add Column tab -> Index Column and create column starting from 1 with increamenting value as 1. 

 

Column 2 Calculation:

Create column 2 with the following calculation:

Column =
IF(
'Time'[Date] = MINX('Time', 'Time'[Date]),
0,
IF(
'Time'[Date] = MAXX('Time', 'Time'[Date]), 1,
DIVIDE(1, 'Time'[Index.1],BLANK())
)
)

Once this is done then go to the visualizations pane -> format -> data colors -> click on the white box under Default color. Then select the following options:
 
diverging (1).png

 

This worked fine for me on my sample data.

 

Thanks,

Vinay Dandwani

View solution in original post

Highlighted
Frequent Visitor

Re: Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

Hi Vinay,

 

Instead of using index column, I would like to rank the date.

By any chance, do you know how to rank the date without duplicate ranking? 😥

Highlighted
Advocate I
Advocate I

Re: Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

Hi Jamie,

Please try these methods, if it works:

Method 1:

https://forum.enterprisedna.co/t/rank-unique-ranking-when-there-are-duplicate-values/2815/5

 

Method 2:

https://community.powerbi.com/t5/Desktop/Rank-values-without-duplicate-ranking/m-p/702312

 

I forgot to mention earlier, if you are using my method then please first sort the columns based on date in edit queries and then create index column.

 

Thanks,

Vinay Dandwani

Highlighted
Frequent Visitor

Re: Conditional Formatting on Data Color For Date (as Legend) in Scatter Chart

Hi Vinay,

 

Got it! Thanks for Sharing 😊

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors