Forum Discussion

Jamie_wong's avatar
Jamie_wong
Frequent Visitor
6 years ago
Solved

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?

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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:
     

     

    This worked fine for me on my sample data.

     

    Thanks,

    Vinay Dandwani

6 Replies

  • venal's avatar
    venal
    Memorable Member
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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:
       

       

      This worked fine for me on my sample data.

       

      Thanks,

      Vinay Dandwani

      • Jamie_wong's avatar
        Jamie_wong
        Frequent Visitor
        6 years ago

        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? ðŸ˜¥