cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jamie_wong
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
Anonymous
Not applicable

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
venal
Memorable Member
Memorable Member

@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?

Anonymous
Not applicable

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

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? 😥

Anonymous
Not applicable

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

Hi Vinay,

 

Got it! Thanks for Sharing 😊

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors