## Dynamic Coloring in Scatterplot based on Unit Scoring in Power BI

##### Description

This project focuses on manipulating data in Power BI, utilizing DAX to apply conditional formatting based on specific criteria. The main objective is to color markers in a scatterplot according to the Unit Scores across different Paybands and Ratings. The expression needs to determine whether markers should be colored Orange, Red, or Blue based on these scores, ensuring a comprehensive and visually intuitive data visualization.

The original prompt:

Can you review the attached DAX Expression I have and rewrite it to accomplish the following? The excel file has tables loaded in Power Query. The DAX Expression will be used in Power BI to format the marker color in a scatterplot based on the IF Statements. If a Unit has any Payband (Supervisor, Journeyman, Apprentice) with a Unit Score less than or equal to zero, then the marker color will be Orange. If a Unit has an overall Rating Unit Score less than or equal to zero, then the marker color will be Red. Rating consists of several Unique Ratings (AD, AE, AM, AME, AN, AA, AR, AO, AS, AT, AZ, PR) in each Payband (Supervisor, Journeyman, and Apprentice). The measure needs to look at each Unit, group each individual rating within a Unit to look sum to average the Unit Score for each Rating. If a Unit does not have a Payband and or a Rating less than or equal to zero, then the marker color is blue. Color_By_Payband and Rating = VAR UnitPaybandCheck = CALCULATE( MINX( SUMMARIZE( 'fSailorAMEXData', 'dUnits'[UIC], 'dPaybands'[Payband], "PointsDiffPayband", [UnitPoints]-[DeployPoints] ), [PointsDiffPayband] ), REMOVEFILTERS('dPaybands'[Payband]) ) VAR UnitRatingCheck = CALCULATE( MINX( SUMMARIZE( 'fSailorAMEXData', 'dUnits'[UIC], 'dRatings'[Rating], "PointsDiffRating", [UnitScore] ), [PointsDiffRating] ), REMOVEFILTERS('dRatings'[Rating]) ) RETURN IF( UnitPaybandCheck < 1, "Orange", ) IF( UnitRatingCheck < 0, "Red", "Blue" ) The DAX expression, as currently written, is not returning the results I have requested.

# Introduction to Conditional Formatting in Power BI

## Implementation of Conditional Formatting in a Power BI Scatterplot Using DAX Expressions

### Step-by-Step Guide

**Setting Up Your Data**Ensure you have a dataset loaded into Power BI. For example, consider a dataset

`SalesData`

with columns`SalesAmount`

,`Profit`

, and`Region`

.**Creating the Scatterplot**- Open Power BI Desktop.
- From the
`Visualizations`

pane, select the scatterplot icon. - Drag
`SalesAmount`

to the`X-Axis`

. - Drag
`Profit`

to the`Y-Axis`

.

**Applying Conditional Formatting Using DAX**To apply conditional formatting, DAX expressions will be used to create calculated columns or measures.

Create a new calculated column that assigns different values based on certain conditions.

`SalesDataCategorized = ADDCOLUMNS( SalesData, "Category", SWITCH( TRUE(), SalesData[Profit] > 10000, "High Profit", SalesData[Profit] > 5000, "Moderate Profit", "Low Profit" ) )`

**Adding Category to the Scatterplot**- Drag the
`Category`

field to the`Legend`

field of the scatterplot.

- Drag the
**Conditional Formatting in the Scatterplot**- Click on the scatterplot visual.
- In the Visualizations pane, find the
`Format`

section. - Expand the
`Data colors`

section. - Under the values section, assign colors to your categories:
`High Profit`

: Choose a color, e.g.,`Green`

.`Moderate Profit`

: Choose a color, e.g.,`Yellow`

.`Low Profit`

: Choose a color, e.g.,`Red`

.

**Adjusting Marker Shapes (Optional)**You can further customize the scatterplot by adjusting the marker shapes for each category:

- In the Visualizations pane, find the
`Shapes`

section. - Assign different shapes to each category if desired.

- In the Visualizations pane, find the

### Final Result

Now, your scatterplot will visually represent the profit categories using different colors based on the conditions defined in your DAX expression. Higher profits will be shown in green, moderate profits in yellow, and low profits in red, providing an immediate visual insight into the data.

### Conclusion

With this approach, you can effectively implement conditional formatting in Power BI using DAX expressions, enhancing data visualization and making it more actionable. This method allows for clear differentiation and analysis of different data segments directly within the scatterplot visualizations.

### Next Steps

In the subsequent units, we will explore more advanced conditional formatting techniques and their applications in different types of Power BI visualizations.

# A Practical Guide to Implementing Conditional Formatting in a Power BI Scatterplot using DAX Expressions

## Understanding and Writing DAX Expressions for Conditional Formatting

### Step 1: Create a Color Measure Based on a Condition

To implement conditional formatting in your Power BI scatterplot, you need to create a DAX measure that will determine the color based on a condition.

Here is an example DAX expression to create this measure. In this case, we are using a hypothetical dataset where we need to color the dots based on a `Sales`

threshold:

```
Color Measure =
IF(
SUM('SalesTable'[Sales]) > 100000,
"Red",
"Blue"
)
```

In this DAX expression:

`SUM('SalesTable'[Sales])`

: Aggregates the sales values.`IF(...)`

: Checks if the aggregated sales are greater than 100,000.`"Red"`

: Assigns the color Red if the condition is met.`"Blue"`

: Assigns the color Blue if the condition is not met.

### Step 2: Apply the Measure to the Scatterplot

- Open your Power BI Desktop.
- Add a scatterplot to your report.
- Assign the required fields to the scatterplot — for example,
`X Value`

,`Y Value`

,`Category`

, etc. - Navigate to the
`Format`

tab of the scatterplot visuals pane.

### Step 3: Configure Data Colors

- In the visualizations pane, find the
`Data colors`

section under the`Format`

pane. - Click on the fx (conditional formatting) button beside
`Data colors`

.

### Step 4: Set the Field Value

- A new window will open, allowing you to configure the color based on a rule or a value.
- Choose
`Field value`

driven:- In the
`Based on field`

dropdown, select the`Color Measure`

that you created earlier.

- In the

### Step 5: Review the Conditional Formatting

- After applying the color measure, the dots on your scatterplot should now color based on the condition specified in your DAX expression.
- Validate that the colors correctly represent the conditions you set.

### Complete Example

For the purpose of a concise yet practical guide, the goal might be to enhance the DAX measure to integrate more conditions. Here's a more comprehensive DAX measure:

```
Dynamic Color Measure =
SWITCH(
TRUE(),
SUM('SalesTable'[Sales]) > 200000, "Green",
SUM('SalesTable'[Sales]) > 100000, "Red",
"Blue"
)
```

In this enhanced expression:

`SWITCH(TRUE(), ...)`

: Evaluates the conditions in sequence.- The first condition checks if the aggregated sales are greater than 200,000 and assigns the color Green.
- The second condition checks if the aggregated sales are greater than 100,000 and assigns the color Red.
- If none of the above conditions are met, it assigns the color Blue.

By following these steps, you can effectively apply conditional formatting to a Power BI scatterplot using DAX expressions and make your visualization more insightful.

Remember, each measure and its conditions can be tailored to fit the specific needs and context of your dataset. Ensure that your DAX expressions are tested and validated within your Power BI environment to see the intended visual outcomes.

# Implementing and Testing Conditional Color Logic in Visuals

## Overview

This unit will guide you through the implementation and testing of conditional color logic in a Power BI scatterplot using DAX expressions to enhance data visualization based on specific criteria.

## Implementation Steps

### Step 1: Create DAX Measures for Conditional Logic

First, create a new measure in the data model that will dictate the color logic based on your specific conditions. Here is an example of a DAX measure to apply different colors based on sales performance:

```
ColorLogic =
SWITCH(
TRUE(),
SUM('Sales'[Amount]) > 1000000, "#00FF00", // Green for sales > 1,000,000
SUM('Sales'[Amount]) > 500000, "#FFFF00", // Yellow for sales > 500,000
"#FF0000" // Red for sales <= 500,000
)
```

### Step 2: Apply Conditional Formatting to the Scatterplot

Next, apply the conditional formatting in the Power BI scatterplot visual.

**Select the Scatterplot Visual:**- Click on the scatterplot visual on the report canvas where conditional formatting is to be applied.

**Add Measure to Field Well:**- Drag the
`ColorLogic`

measure into the 'Legend' or 'Values' well of the scatterplot based on your requirement.

- Drag the
**Configure Data Colors:**- Click on the scatterplot visual to select it.
- In the Visualizations pane, go to the
**Format**section. - Expand the
**Data colors**option. - Click on the fx (function) button next to the 'Default color' or each category within 'Legend'.
- In the
**Default color**popup, select**Field value**from the format by dropdown. - Choose the measure
`ColorLogic`

from the dropdown for**Based on field**. - Click
**OK**to apply.

### Step 3: Validate the Implementation

To ensure that the conditional logic has been correctly implemented:

- Verify that the scatterplot points change colors according to the conditions set in the
`ColorLogic`

DAX measure. - Check multiple points to ensure that all expected colors appear correctly based on their corresponding conditions.
- Cross-check the sales data with the applied colors to affirm that the logic correctly relates to the intended data points.

## Conclusion

Following these steps, you will successfully implement conditional color logic in a Power BI scatterplot using DAX expressions. Testing involves verifying the visual changes against the set criteria to ensure accurate representation of data insights. This enhances the scatterplot’s effectiveness in conveying critical information at a glance.