Explore Power BI Core Visualizations: Part 4 – Table and Matrix Visuals

March 17, 2025March 17, 2025emguyant Leave a comment

Chart your data journey! Transform data into insights with Power BI core visualizations.

Table and Matrix visuals in Power BI are essential for presenting detailed and structured data. Both visuals excel at displaying large amounts of information while highlighting trends, relationships, and hierarchies.

Tables offer a simple way to present data in a straightforward tabular format and are best for displaying detailed row-level data, such as sales records, inventory lists, or customer information. Unlike other visuals that summarize data at a high level, tables retain all details, making them an excellent tool for deep-dive analysis.

Matrix visuals enhance this capability by grouping and summarizing data hierarchically. Unlike tables that present data in a flat structure, matrix visuals allow for expandable rows. Users can then collapse or expand these groupings interactively to meet their needs.

Table and Matrix visuals are excellent for presenting data and hierarchical summaries, but they may not be suitable for every situation. It’s important to choose the right visual for effective reporting. To discover other essential visuals, check out Parts #1, #2, and #3 of the Explore Power BI Core Visualizations series.

Explore Power BI Core Visualizations: Part 1 – Bar and Column Charts

Chart your data journey! Transform data into insights with Power BI core visualizations.

Explore Power BI Core Visualizations: Part 2 – Line and Area Charts

Chart your data journey! Transform data into insights with Power BI core visualizations.

Explore Power BI Core Visualizations: Part 3 – Pie, Donut, and Treemap

Chart your data journey! Transform data into insights with Power BI core visualizations.

Customizing Table Visuals

Table visuals in Power BI can be challenging to read and may become overwhelming if not formatted properly. Power BI offers a range of properties that allow us to customize our table visuals, enhancing readability, improving structure, and dynamically highlighting key insights.

Formatting the Table Layout

The fundamentals of formatting our table visuals involve customizing the grid, values, column headers, and totals properties. By adjusting these elements, we can enhance the appearance and clarity of our table visuals, making them more readable for our viewers.

The grid properties of our table visuals dictate how rows and columns are separated, providing a structured appearance.

We can toggle the horizontal and vertical gridlines on or off based on the requirements of the visual and the desired level of separation between rows and columns. Enabling the gridlines results in a structured layout, while disabling them offers a more minimalist design.

Additionally, the borders property allows us to define and apply borders to various sections of the table visual, including the column header, values section, and totals section.

In this example, we enable both the horizontal and vertical gridlines and set the bottom border of the column header section to an accent green color.

Next, we will focus on the Values property section. This area allows us to customize text styles and background colors, essential for ensuring our tables are readable and visually engaging.

We can specify the primary text color, background color, and alternate colors to differentiate between alternating rows in the visual. Alternating the styles of the rows improves readability and makes it easier to track values across each row.

The final basic formatting properties to consider are the column headers and totals. The column header properties allow us to adjust the font size, background color, and text alignment, which helps create a structured and easy-to-read table.

When we enable the Totals property, our table will include a row that aggregates the values from each column. We can customize this row’s font style, text color, and background color to distinguish it from the standard data rows.

In our example, we set the column headers to white with a dark blue background, making the header row easily identifiable. Additionally, we activate the totals row, giving it dark blue text, a gray background, and top and bottom borders to further differentiate it from the other data rows.

Enhancing Tables with Advanced and Conditional Formatting

In addition to basic formatting, Power BI provides multiple settings and options for advanced and conditional formatting, allowing for greater control over the appearance of table visuals. These options enable targeted formatting for specific columns and dynamic styling for key data points and trends.

We can utilize the specific column properties to apply unique formatting to individual columns, offering the flexibility to adjust the styling of headers, values, and total values for different series.

For example, we can include the count of transactions alongside our sales data in the table. Using the specific column properties, we can set the text color of the values and totals to visually distinguish them from the primary focus of the sales amounts.

The cell elements properties offer additional customization options and enable dynamic formatting. These customizations enhance our ability to highlight trends and identify key values.

For instance, using these properties, we can conditionally format the background and text color of the Sales Year-over-Year (YoY) series. This lets us quickly see which products have experienced growth or decline compared to the previous year.

We enable data bars for the current and last year’s sales series, visually comparing sales values across different products.

We also activate the icons feature for the current year’s sales values. We add a trending upward arrow for products whose current-year sales exceed those of the previous year and a downward arrow for products whose current-year sales are lower than last year’s. This visual representation quickly indicates product sales growth next to each product category and complements the Sales Year-over-Year series.

By integrating fundamental concepts, specific column formatting, cell elements, and conditional formatting, our Power BI table visuals can become dynamic and intuitive report elements that lead users to insights.

Customize Matrix Visuals

The Matrix visual in Power BI shares many of the same formatting options as the Table visual, including Grid, Values, Column headers, Specific column, and Cell elements formatting. However, Matrix visuals introduce additional formatting options for the hierarchical data structure.

Row Headers

Row headers are a key feature of Matrix visuals, allowing viewers to group and drill into the data. Power BI lets us define the font styling, text alignment, text color, and background color. We can also customize the size and color of the expand and collapse (+/-) buttons, or not show them all together.

Subtotals and Grand Totals

Row subtotals summarize data at each hierarchical level, allowing viewers to see how individual categories contribute to the overall total.

By toggling a setting in the Format options, we can enable or disable row subtotals. When row subtotals are enabled, we can customize the font and background styling and define the total label and its position.

In our example, we enable row subtitles and adjust the styling to ensure consistency with other visual elements. We then set the position of the subtotal to the bottom and activate the row-level settings. Under these settings, we select Product Code and label it as “Subtotal.” Next, we choose Product and label it “Grand Total.”

Grand totals display the final sum of all row and column values in our Matrix visuals. Proper formatting ensures these totals remain distinct and easy to locate.

The formatting options include font styling, font color, and background color.

Advanced Techniques and Customizations

The table and matrix visuals in Power BI provide a range of options for creating unique presentations that enhance data visualization, interactivity, and analytical depth. By designing these visuals thoughtfully, we can highlight key insights, delve deeper into our data, and create dynamic reports tailored to the viewers’ needs. Let’s explore some advanced examples that go beyond the basics.

Clean and Straightforward Sales Totals

This matrix visual presents a structured overview of our sales totals across different regions, product categories, and years. The clear and straightforward presentation makes analyzing trends over time and across regions easy.

We add a matrix visual to our report canvas to create this visual. Next, we place the Region and Product data into the Rows field, the Year into the Columns field, and a Total Sales measure into the Values field. After that, we expand all rows by one level and position the row subtotals at the bottom. Finally, we change the label for the Product row level label to Subtotal.

In the Layouts and Style Presets options, we set the Style to “None” and the Layout to “Outline.” We also toggled off the Repeat Row Headers option.

Under the Values properties, we adjust the background color and alternate background color to match the color of the matrix background.

Next, we format our column headers and the grand total section.

Column Headers:

Background color: matrix background color
Font color: dark blue font color
Font style: semi-bold and font size 11

Row/Column Grand Total:

Background color: a darker tone of the matrix background color
Font color: the same dark blue used for the values

To complete the visual, under the Cell elements properties, we enable data bars and set the positive bar color to a tone of the matrix background, ensuring that the bars and values are easy to read.

Month and Day of Week Sales Heat Map

We can use the Power BI matrix visual to create a heat map that visually displays sales performance across different days of the week and months. This heat map is created using the conditional formatting feature for cell elements, effectively highlighting patterns in our sales distribution.

We place a month name series in the rows field, a weekday name abbreviation series in the columns field, and a total sales measure in the values field.

Then, we toggle off the row and column subtotals.

To create a heat map, we start by enabling the background color property under the Cell elements section.

In the Background Color dialog box, we set the Format Style to gradient based on our Total Sales measure. Next, we add a middle color and set the colors for both the minimum and center values to match the matrix background, while the color for the maximum value is set to a dark blue. Including a middle color helps emphasize the top performances in the heat map.

Next, we enable the font color property under Cell elements.

In the Font Color dialog box, we set the Format Style to gradient based on our Total Sales measure. We then add a middle color and set both the minimum and maximum values to match the background color of the matrix. For the center value, we select a slightly darker shade. By setting these colors, we can hide values close to the minimum, gradually reveal values as they approach the maximum, and ensure that the light color of the maximum stands out against the dark blue background.

Product Review Ratings with Custom SVG Icons

This matrix visual uses custom SVG icons to show average product ratings by region, product, and product code.

We start by adding the Product and Product Code columns to the Rows fields, the Region column to the Columns field, three measures to the Values fields, and applying general formatting to the column headers.

The measures are:

Score SVG = 
VAR prefix = MAXX(FILTER(Icons, Icons[Name]="prefix"), Icons[SVGIcon])
VAR _00 = MAXX(FILTER(Icons, Icons[Name]="Satisfaction0.0"), Icons[SVGIcon])
VAR _05 = MAXX(FILTER(Icons, Icons[Name]="Satisfaction0.5"), Icons[SVGIcon])
VAR _10= MAXX(FILTER(Icons, Icons[Name]="Satisfaction1.0"), Icons[SVGIcon])
VAR _15= MAXX(FILTER(Icons, Icons[Name]="Satisfaction1.5"), Icons[SVGIcon])
VAR _20= MAXX(FILTER(Icons, Icons[Name]="Satisfaction2.0"), Icons[SVGIcon])
VAR _25= MAXX(FILTER(Icons, Icons[Name]="Satisfaction2.5"), Icons[SVGIcon])
VAR _30= MAXX(FILTER(Icons, Icons[Name]="Satisfaction3.0"), Icons[SVGIcon])
VAR _35= MAXX(FILTER(Icons, Icons[Name]="Satisfaction3.5"), Icons[SVGIcon])
VAR _40= MAXX(FILTER(Icons, Icons[Name]="Satisfaction4.0"), Icons[SVGIcon])
VAR _45= MAXX(FILTER(Icons, Icons[Name]="Satisfaction4.5"), Icons[SVGIcon])
VAR _50= MAXX(FILTER(Icons, Icons[Name]="Satisfaction5.0"), Icons[SVGIcon])

RETURN
SWITCH(
    TRUE(),
    [Average Score]<0.5, prefix&_00,
    [Average Score]>=0.5 && [Average Score]<1.0, prefix&_05,
    [Average Score]>=1.0 && [Average Score]<1.5, prefix&_10,
    [Average Score]>=1.5 && [Average Score]<2.0, prefix&_15,
    [Average Score]>=2.0 && [Average Score]<2.5, prefix&_20,
    [Average Score]>=2.5 && [Average Score]<3.0, prefix&_25,
    [Average Score]>=3.0 && [Average Score]<3.5, prefix&_30,
    [Average Score]>=3.5 && [Average Score]<4.0, prefix&_35,
    [Average Score]>=4.0 && [Average Score]<4.5, prefix&_40,
    [Average Score]>=4.5 && [Average Score]<=5.0, prefix&_50
)

Average Score = 
AVERAGE(Reviews[SatisfactionScore])

Review Count = 
COALESCE(COUNTROWS(Reviews), 0)

We enable column and row subtotals and format the grand totals section to visually distinguish it from the main data.

This visual enhances the user experience by making the review score data more intuitive to explore and understand.

These examples demonstrate how Power BI’s Table and Matrix visuals can be used and customized to improve our reports. By leveraging these visuals and the customization options they offer, we can create engaging, insightful, and easy-to-interpret reports.

Wrapping Up

Table and Matrix visuals in Power BI are effective tools for presenting structured data, whether through detailed tables or hierarchical matrices. By applying formatting and customization techniques, these visuals can transform our data and provide clear and intuitive insights.

Advanced features such as drill-down capabilities, cell element customization, and conditional formatting enhance these visuals beyond merely presenting numbers, making them more interactive and visually engaging. Table and Matrix visuals offer the flexibility to meet a variety of our reporting needs.

If you’d like to follow along and practice these techniques, sample data, and a Power BI report template file are available here: GitHub – EMGuyant Power BI Core Visuals.

Thank you for reading! Stay curious, and until next time, happy learning.

And, remember, as Albert Einstein once said, “Anyone who has never made a mistake has never tried anything new.” So, don’t be afraid of making mistakes, practice makes perfect. Continuously experiment, explore, and challenge yourself with real-world scenarios.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.

Power BI Feature Spotlight: Multi-Select Text Slicer, Better Treemaps, and TMDL View

February 14, 2025February 14, 2025emguyant Leave a comment

Stay ahead of the latest features with a focused look at Power BI’s latest features, their benefits, and use cases. Dive into walkthroughs and tips to enhance your reports.

The Power BI update for January 2025 introduces several exciting features that improve report interactivity, visualization, modeling, and development efficiency.

This post will highlight three key updates: improvements to the text slicer, upgrades to the treemap visual, and the preview of TMDL scripting experience.

The text slicer (preview) was introduced in November 2024, now allows mutli-selection, addressing a major limitation in text-based filtering. The treemap visual gains new tiling and spacing formatting options and TMDL view previews a code-first approach to semantic modeling.

Text Slicer (Preview) Enhancements

In my previous post, Power BI Feature Spotlight: Data Filtering and Modeling, I introduced the Text Slicer, highlighting its benefits, use cases, and the limitation of allowing only a single text input.

The January 2025 update addresses this limitation by introducing an option for users to input multiple values. With this new feature, users can now add multiple text inputs to the slicer, enabling them to make multiple selections for filtering the dataset.

The text slicer now includes a Allow multiple values toggle in the format settings for allowing multiple values. For more details and how to enable the text slicer (preview) see: Enhancement to Text slicer (Preview).

By allowing multiple text values, this update enhances flexibility and gives users greater control over data slicing and insight generation.

Use Case: Applying the Text Slicer in Reports

Filter on Parts of a Product Code – Explore data without a standalone field

Many datasets store information in a single field, such as a product code that includes details like color, size, or category. The new text slicer with multi-selection functionality makes filtering easier based on multiple embedded attributes.

For example, in the sample dataset, product codes contain embedded color codes (e.g. SM-5933-BK, where BK stands for black). Previously, users could only filter by one color code at a time. Now, users can select multiple color codes simultaneously to display all products that match their desired colors.

This enhancement enables better utilization of existing data structures, enhancing report filtering efficiency and flexibility without requiring additional transformations.

Search and Filter Product Reviews – Analyze long-form text fields

The text slicer was already a powerful tool for filtering customer reviews by keyword. Now, with the introduction of multi-selection, users can dive deeper and gain insights across multiple topics at the same time.

For instance, when working with a dataset containing product reviews, we could previously filter by a single keyword like “battery” to see all related reviews. With the new update, we can now filter for both “battery” and “charging” simultaneously.

A Better Treemap: The Latest Enhancements Explained

The January 2025 Power BI Desktop update enhances the treemap visual, providing greater control and customization, ensuring treemaps remain an effective tool for visualizing hierarchical data. We can now adjust the tiling method and spacing controls within the visual’s Layout properties.

New Tiling Methods: More Control Over Treemap Layouts

Squarified: Uses a squarified treemap algorithm to create a balanced layout where rectangles maintain an aspect ratio close to squares. This method prevents elongated rectangles, improving size comparisons and readability.

Binary: Continuously divides the chart area into two sections, incrementally adding new rectangles and creating a balanced format. Each hierarchy level is split further, resulting in a well-organized treemap that adjusts to the underlying data structure.

Alternating (Columns, Rows): Distinguish categories by splitting them into columns, and each is split into rows. This method is effective at visualizing data at multiple hierarchical levels.

New Spacing Options: Improved Readability and Appearance

Space between all nodes: Introduces gaps between adjacent nodes at all hierarchy levels, reducing visual clutter and improving clarity.

Space between groups: Adds extra space around each node group, helping to separate different categories visually.

For more information and details check out the Enhancements to Treemap visual section of the January 2025 update feature summary. If you are looking to dive in and get hands-on with this update, take a look at the 2025 Week 7 Power BI Workout Wednesday challenge. This challenge uses the new treemap feature to create a treemap visual organized into columns.

TMDL Scripting Experience (Preview)

The TMDL view is a new feature added to Power BI Desktop and has gotten a lot of attention for good reason.

TMDL view offers a scripting environment that enables developers to script, modify, and implement changes to the semantic model using Tabular Model Definition Language (TMDL). This view provides an alternative experience for semantic modeling in Power BI Desktop, allowing users to work with code rather than relying solely on the user interface.

The key benefits of the TMDL scripting experience include:

Enhanced Development Efficiency: The code editor includes features such as search-and-replace and support for multi-line edits, streamlining the coding process.
Increased Reusability: TMDL scripts allow for scripting, sharing, and reusing semantic model objects, making it easier to manage and replicate work.
Greater Control and Transparency: This feature exposes all semantic model objects and properties, enabling users to set or modify elements that may not be accessible through the Power BI Desktop user interface.

Interested to learn more about this feature? Check out the TMDL scripting experience (Preview) feature summary, the How to Use TMDL View in Power BI Desktop – Top Tricks! video, and this great blog post TMDL View And Power BI Developer Productivity: An Example Using The Detail Rows Definition Property.

Wrapping Up

The January 2025 Power BI update delivers significant updates that improve report interactivity, usability, and development efficiency. The new multi-select capability of the text slicer removes a key limitation, and the treemap visual improvements provide greater control, helping us make our report more intuitive.

The introduction of the TMDL scripting experience unlocks tools directly in Power BI Desktop to adopt a code-first approach to semantic modeling, providing greater reusability.

As Power BI continues to evolve, the updates provide us with the tools necessary to create more dynamic, interactive, and insightful reports.

Check out the Power BI January 2025 Feature Summary for more details and updates.

Thank you for reading! Stay curious, and until next time, happy learning.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.

Enhancing Bar Charts in Power BI: A 2025 Workout Wednesday Challenge

February 3, 2025emguyant Leave a comment

Make your Power BI reports more impactful—learn how to highlight key data points in a bar chart!

Workout Wednesday is a weekly challenge series designed to help develop Power BI skills through hands-on exercises.

This guide outlines my solution for the Power BI 2025 Week 5 challenge, which focused on adding an All category to a bar chart in Power BI. The challenge emphasized data transformation, visualization, and dynamic formatting to enhance insights.

The Challenge Requirements

The 2025 Week 5 Power BI challenge involved creating a bar chart that displays the unadjusted percent change in the consumer price index from December 2023 to December 2024 across various categories. Here are the challenge requirements:

Add a “total average” row to the data that contains the average of the unadjusted_percent_change values in the original data set.
Plot the items and associated percent increase in a bar chart. Sort the items by descending value of unadjusted_percent_change.
Add data labels to the bar chart to show the exact percent change for each item.
Use a different bar color for eggs to make it stand out. Also, use a different color for your total average to make it look distinct from the other items.
Use a canvas background color or image related to eggs.

The Final Result

Before we start the step-by-step guide, let’s look at the final result.

The original data sources are BLS and USDA, and the data used for this challenge is hosted on Data.World. The background image is a photo by Gaelle Marcel on Unsplash.

Adding a Total Average Row in Power Query

The initial step involved loading and transforming the raw dataset in the Power Query Editor, where a total average row was added. This row calculates the average unadjusted percent change values and acts as a benchmark for comparison.

Here is the Power Query used to complete this step.

let
    Source = Excel.Workbook(File.Contents("C:\temp\PBIWoW2025W5.xlsx"), null, true),
    data = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    setHeaders = Table.PromoteHeaders(data, [PromoteAllScalars=true]),
    
    // Adjust "Unadjusted Percent Change" by converting values to percentages
    adjustPercentages = Table.TransformColumns(setHeaders, {{"Unadjusted Percent Change", each _*0.01}}),
    
    // Calculate the total average and create a new row
    totalAverage = List.Average(adjustPercentages[Unadjusted Percent Change]),
    averageRow = #table(
        Table.ColumnNames(setHeaders), 
        {{"Total average", totalAverage}}
    ),

    //Append Total Average to the initial dataset
    finalTable = Table.Combine({adjustPercentages, averageRow}),
    
    setDataTypes = Table.TransformColumnTypes(finalTable,{{"Item", type text}, {"Unadjusted Percent Change", Percentage.Type}})
in
    setDataTypes

Once the data is loaded, Power Query converts the percent values so they display correctly when the data type is set to Percentage.Type.

It then calculates the average of the unadjusted percent change data using the List.Average() function, which computes the average of all the values in the unadjusted percent change column. Once calculated, we create a single-row table using #table() to ensure the structure matches the initial dataset. In this table, Total Average is set for the Item column, and the calculated average is in the Unadjusted Percent Change column.

Lastly, the Power Query appends this row to our initial dataset using Table.Combine() and sets the column data types.

Creating the Bar Chart and Sorting the Data

With the Total Average data now included in the dataset, the next step was creating the Power BI bar chart to visualize the data.

The visual is a clustered bar chart, where Item is set for the y-axis and Unadjusted Percent Change is set for the x-axis.

The axis is sorted in descending order by Unadjusted Percent Change and data labels are enabled.

Additional formatting steps included disabling the titles for the x- and y-axes, darkening the vertical gridlines, and removing the visual background.

At this point, the bar chart displays all categories, including the Total Average row, but the colors are uniform. The next step is to apply conditional formatting using DAX to highlight key insights to improve clarity.

Applying Conditional Formatting Using DAX

The bar colors differentiate key categories to make the visualization more insightful.

Values above the average should be highlighted to stand out*.
The average value should have a distinct color to serve as a benchmark.
Values below the average should have a uniform color.

* This only applies to the Eggs category in the current data set. Although this doesn’t strictly meet the requirement of explicitly making the Eggs category stand out, it remains dynamic. It will highlight any value in the future that would be above the average.

Here is the DAX measure:

Bar Color = 
VAR _totalAverage = 
    COALESCE(
        LOOKUPVALUE(
            pbiwow2025w5[Unadjusted Percent Change], 
            pbiwow2025w5[Item], 
            "Total average"
        ), 
        0
    )
VAR _value = 
    COALESCE(
        SELECTEDVALUE(pbiwow2025w5[Unadjusted Percent Change]), 
        0
    )
RETURN
SWITCH(
    TRUE(),
    _value < _totalAverage, "#ecc88f",
    _value > _totalAverage, "#183348",
    _value = _totalAverage, "#ad6b1f"
)

The measure looks up the total average value and retrieves the Unadjusted Percent Change value of the category within the current evaluation context.

Then, using the SWITCH() function, the color code is set based on whether the current _value is less than, equal to, or greater than the total average.

Applying the DAX Measure to the Bar Chart

Select the visual on the report canvas.
In the Format pane, locate the Bars sections.
Click the fx (Conditional Formatting) button next to the Bar Color property.
In the Format style drop-down, select Field value, and in the What field should we base this on? select the newly created Bar Color measure.

To also have the data label match the bar color, locate the data labels section in the Format pane and the Values section. Follow the same steps to set the color of the data label value.

The visual is now structured to highlight key categories based on their relationship to the Total Average value.

BONUS: Creating Dynamic Titles with DAX

To improve the visualization, a dynamic subtitle can be added. This subtitle automatically updates based on the dataset, providing insights at a glance.

I start by creating the DAX measure:

Subtitle = 
VAR _topPercentChange = 
    TOPN(1, pbiwow2025w5, pbiwow2025w5[Unadjusted Percent Change], DESC)
VAR _topItem = 
    MAXX(_topPercentChange, pbiwow2025w5[Item])
VAR _topValue = 
    MAXX(_topPercentChange, pbiwow2025w5[Unadjusted Percent Change])
VAR _average = 
    COALESCE(
        LOOKUPVALUE(
            pbiwow2025w5[Unadjusted Percent Change], 
            pbiwow2025w5[Item], 
            "Total average"
        ), 
        0
    )
VAR _belowAverage =
    ROUNDUP(
        MAXX(
            FILTER(pbiwow2025w5, pbiwow2025w5[Unadjusted Percent Change] < _average), 
            pbiwow2025w5[Unadjusted Percent Change]
        ), 
        2
    )

RETURN
_topItem & " prices have increased by " 
& FORMAT(_topValue, "0.0%") & ", exceeding the average of " 
& FORMAT(_average, "0.00%") & ", while other categories remain under " 
& FORMAT(_belowAverage, "0%") & "."

The measure identifies the category with the highest percentage change, extracting both the item name and the percent change value. It then retrieves the total average value to incorporate into the title. Next, it finds the highest percent change value for all the items that fall below the average and rounds the value up.

Finally, the RETURN statement constructs a text summary that displays the category with the highest price change, its percentage change, a comparison to the total average, and a summarized value for all items below the average.

Applying the Dynamic Subtitle

Select the visual on the report canvas.
In the Format pane, locate the Title section.
Under the Subtitle section, select the fx button next to the Text property.
In the Format style drop-down, select Field value, and in the What field should we base this on? select the newly created Subtitle measure.

This subtitle provides quick insights for our viewers.

Wrapping Up

This guide outlines my approach to completing the Workout Wednesday 2025 Week 5 Power BI Challenge, focusing on essential data transformation and visualization techniques.

Now that you have seen my approach, how would you tackle this challenge? Would you use a different Power Query transformation method, a different visualization style, or an alternative approach to dynamic formatting?

For complete challenge details, visit Workout Wednesday – 2025 Week 5.

If you’re looking to grow your Power BI skills further, be sure to check out the Workout Wednesday Challenges and give them a try!

Thank you for reading! Stay curious, and until next time, happy learning.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.

Explore Power BI Core Visualizations: Part 3 – Pie, Donut, and Treemap

September 19, 2024March 17, 2025emguyant Leave a comment

Chart your data journey! Transform data into insights with Power BI core visualizations.

Pie, Donut, and Treemap charts can be helpful tools when we need to show how different parts contribute to a whole. Pie charts represent data as slices of a circle and show the relationship of parts to a whole. Donut charts are similar to pie charts. However, their center is blank, providing space to add a label or other icon. Treemap charts use nested rectangles to visualize each level of hierarchy in our data.

These charts can be helpful for specific visualizations, but it is important to recognize their limitations. As with all of our data visualization tasks, choosing the right type of visual is essential. To explore bar and column charts, visit Part #1 of this series, and to learn more about line and area charts, check out Part #2.

Explore Power BI Core Visualizations: Part 1 – Bar and Column Charts

Chart your data journey! Transform data into insights with Power BI core visualizations.

Explore Power BI Core Visualizations: Part 2 – Line and Area Charts

Chart your data journey! Transform data into insights with Power BI core visualizations.

Customizing Pie Charts

Customizing our pie charts enhances their clarity, making it easier for our report viewers to interpret the data.

Adjusting Colors

The color choices used in our pie charts play a crucial role in their readability. Within the Slices property of our Pie chart, we can customize the colors used for each slice. When selecting colors, we should ensure they are visually distinct and that the colors used for our data categories are consistent across all visuals on the page and within the report.

We set the color of our slices in the Format pane, under the slices section. Here, we will find each data category included in the chart, and we can explicitly see the color.

Formatting Data Labels

The data labels on our pie charts help convey important insights to our viewers. Within the Data label properties of our pie chart we find several options to edit and format our data labels. We have options to set the position of our data labels (e.g. outside, prefer inside), what information to display such as category name, values, or percent of total, and under the Value grouping, we can format the text of the data labels.

Using a combination of these properties, we can make our charts more informative at a glance. For example, we can improve the above plot by displaying the category name and percent of total directly on our chart.

It is important to be cautious when using data labels. We want to provide essential information without overcrowding the chart with too much text. Adjusting the font size, background, font color, and label position can help us maintain a clean and organized look.

Using Legends Effectively

Our data labels display information directly on our charts, while legends offer a clean way to display what data category corresponds to which chart slice. Within the Legend properties we can set the position of the legend, set the title, and format the text and title of the legend.

We can update the Pie chart above to display the legend and then update the data label to display the total sales value and the percent of total value.

When using pie charts, it is important to limit the number of categories to ensure clarity. Pie charts are most effective when displaying data with no more than a few slices. If this cannot be accomplished, it’s best to consider other visuals to provide our insights.

Customizing Donut Charts

Donut charts provide an alternative to pie charts, with the added benefit of a central blank space. This space can be used for a variety of purposes or left blank. By customizing our donut charts, we can enhance their effectiveness and make them more informative and engaging.

Like pie charts, donut charts allow us to set the color of each chart slice, use data labels to provide additional information and position the legend to provide additional context.

Donut charts also offer additional properties we can customize to enhance these charts even further.

Modifying the Inner Radius

One of the main differences between our pie and donut charts is the open area in the center. Within the Slices property grouping, we will find a Spacing option. Here, we can adjust the inner radius, controlling how wide or narrow the chart’s ring appears.

Increasing this value creates a more pronounced donut shape, which can help draw viewers’ attention to the center of the chart. Decreasing this value makes the chart look more like a traditional pie chart.

Donut charts can be helpful when we need to compare proportions while also displaying an aggregate metric that we can place in the open center of the visual. However, like pie charts it is important to only use these charts when there is a small and manageable number of segments. When there are too many segments, donut and pie charts become cluttered and difficult to interpret.

When using these comparison charts, we should aim for simplicity and clarity.

Customizing Treemap Charts

Treemap charts are useful when visualizing hierarchical data. They allow us to display a large amount of information in a compact space. Each category in the visual is represented by a rectangle, the size of which reflects the category’s value.

Treemap charts offer various properties that we can customize to enhance their clarity, ensuring they effectively convey complex data structures and insights.

Adjusting the Colors

In our treemap visuals, colors distinguish between our different categories. Like in our other charts, we can set the color for each category, and it is important that this color selection is consistent across all visuals within our report. For example, we can create a treemap of our Total Sales by product category and product code and set the colors for each category to ensure visual consistency.

Setting Labels and Viewing Tooltips

Like many of our other Power BI visuals, Treemap visuals allow us to customize visual labels and tooltip properties to improve the readability of our visuals. Labels on our visuals provide context by displaying category names and values directly within each rectangle. By using the options available under the data labels and categories labels we can adjust the font size, color, display units, and the number of decimal places to improve the clarity of our visual.

We will use these properties to improve the treemap above by increasing the font size and weight of the category labels so they are easily identifiable. We will also turn on data labels to provide our viewers with precise information for the largest contributors.

Tooltips are another feature common across our Power BI visuals that adds an interactive element to our reports and can provide our viewers with additional information. When a user hovers over a rectangle, a tooltip appears, providing them with information about that category. Additionally, the information included within the tooltip can be customized to provide further context, such as percentages or other metrics.

In treemaps, some rectangles may be too small to display the data label, and tooltips become a valuable asset for delivering details without cluttering the visual.

Using Drill-Down Features

One strength of treemaps is their ability to represent hierarchical data. Power BI’s drill-down functionality lets us explore these hierarchies in detail. A user can use the drill-down feature to drill into the next level of the hierarchy, focusing on the subcategories or finer details of the data.

Drill-down features provide our viewers with another interactive element, allowing them to explore the dataset at multiple levels without overwhelming them with too much information upfront.

In our example treemap above, the first-level category is Product, and the second is Region. This allows our viewers to drill into a product category to view the distribution of product sales across sales regions.

Treemap charts can be a helpful tool for comparing proportions within hierarchical data. However, they are most effective when the differences in value between categories are significant. When all categories are similar in size, the visual can be difficult to interpret, so it is important to evaluate whether a treemap is appropriate for each use case.

Advanced Techniques and Customizations

Current Year Sales by Product Selection

The Current Year Sales donut chart helps visualize how a selected product contributes to the total current year sales.

To build this visual, we first, create two measures: Total Sales CY and Remaining CY Sales.

The Total Sales CY measure calculates the current year’s total sales, and the Remaining CY Sales calculates the difference between the total sales of all products and the selected product’s sales.

Total Sales CY = TOTALYTD([Total Sales], DateTable[Date])

Remaining CY Sales = 
VAR _allSales = CALCULATE([Total Sales CY], ALL(Sales))

RETURN
_allSales - [Total Sales CY]

We then add these two measures to our donut chart’s Values property and set the color of the Total Sales CY to a dark blue and Remaining CY Sales to a light gray.

We then turn off the legend, and update the data labels to show only the percent of total value.

Next, we create a new measure that will be used for the visual subtitle. The subtitle provides additional context by showing how close the product’s sales are to hitting a sales target, and how this year’s sales compare to last year’s sales for the same period of time.

Product Sales Subtitle = 
VAR _lyYTDSales = 
   TOTALYTD(
      [Total Sales],
      DATESBETWEEN(
         DateTable[Date], 
         DATE(MAX(DateTable[Year])-1, 1, 1), 
         DATE(YEAR(TODAY())-1, MONTH(TODAY()), DAY(TODAY()))
      )
   )
VAR _compare = 
   [Total Sales CY] - _lyYTDSales
VAR _percentDiff =
    _compare/((_lyYTDSales+[Total Sales CY])/2)

RETURN
FORMAT(
   [CY Sales Percent of Target], "0.0%") 
   & " of Sales Target | " 
   & IF(_compare<0, "▼", "▲") 
   & FORMAT(ABS(_percentDiff), "0.0%") & " compared to previous"

Lastly, we use the empty center of the donut chart to provide additional details to our viewers. Here, we display the total sales value for the selected product and add a dynamic label to clearly show what the sales value represents.

To do this we create another measure to dynamically update based on user selections. The Selected Product measure shows “All Products” when there is no product selection; otherwise, it displays the selected product name, or when there are multiple selections, it displays a list of all the selected products.

Selected Product = 
IF (
    ISFILTERED ( Products[Product] ),
    IF (
        HASONEVALUE ( Products[Product] ),
        VALUES ( Products[Product] ),
        CONCATENATEX ( 
            ALLSELECTED ( Products[Product] ), 
            Products[Product], 
            ", "
        ) & " Sales"
    ),
    "All Products"
)

Current Year Regional Sales

The Current Year Regional Sales donut chart compares sales across different sales regions.

We start building this chart with a series of measures. First, a set of measures calculating the current year sales for each region.

Asia Sales CY = CALCULATE([Total Sales CY], Regions[Region]="Asia")
Europe Sales CY = CALCULATE([Total Sales CY], Regions[Region]="Europe")
US Sales CY = CALCULATE([Total Sales CY], Regions[Region]="United States")

Then, we add another set of measures calculating the difference between the total sales and the region-specific sales.

Asia Sales CY Diff = [Total Sales CY] - [Asia Sales CY]
Europe Sales CY Diff = [Total Sales CY] - [Europe Sales CY]
US Sales CY Diff = [Total Sales CY] - [US Sales CY]

Similar to the visual above, we add the region-specific measures to a donut chart and format them to appear as concentric donut charts. This is done by turning off the border and background of the two inner donut charts and resizing them.

Next, we build a customized legend element that not only shows the total sales for each region but also the percentage that each region contributes to the overall sales. The percentage of overall sales is calculated using 3 new measures.

Asia CY % Sales = DIVIDE([Asia Sales CY], [Total Sales CY])
Europe CY % Sales = DIVIDE([Europe Sales CY], [Total Sales CY])
US CY % Sales = DIVIDE([US Sales CY], [Total Sales CY])

This visual is also dynamic based on the user interaction and selections made on the product slicer.

This visual provides our viewers with a quick comparison of regional sales. It clearly shows the sales distribution across our regions, allowing our viewers to identify which regions have the highest or lowest sales.

Wrapping Up

In this part of the series, we explored Pie, Donut, and Treemap charts and how to effectively use and customize these visuals in our Power BI reports. Pie and Donut charts can be helpful tools when displaying proportions but should be used selectively when comparing only a few categories. Treemap charts excel at displaying hierarchical data and provide us with a compact and insightful way to visualize this data.

While these visuals can prove to be just the right fit for a given requirement, they must only be used in the right scenarios, with an emphasis on simplicity and clarity.

In the next part of the series, we will explore Power BI’s Gauge, Card, and KPI visuals. These visuals help us display metrics and provide at-a-glance insights into key data points.

Thank you for reading! Stay curious, and until next time, happy learning.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.

Explore Power BI Core Visualizations: Part 2 – Line and Area Charts

August 11, 2024August 11, 2024emguyant Leave a comment

Chart your data journey! Transform data into insights with Power BI core visualizations.

Welcome back to our exploration of the core visuals in Power BI. In Explore Power BI Core Visualizations Part 1 we explored bar and column charts and how they can be used and customized within our report to provide insights.

Now, we will explore another set of visuals in Power BI: Line and Area charts. These visuals are essential tools for displaying trends and patterns over time, making them ideal for analyzing time series data, monitoring performance, or illustrating cumulative values.

Let’s dive in and explore how to create, customize, and enhance our Line and Area Charts in Power BI to elevate our reports.

Types of Line Charts

Line charts are a fundamental tool in data visualization, especially when it comes to displaying trends and changes over time. A line chart provides our viewers a continuous flow of connected data points that are easy to follow and makes them ideal for visualizing trends.

In Power BI, we have several varieties of line charts, each serving a unique purpose and offering different way to analyze and display different data. Let’s explore these main types.

Line Chart

A standard line chart is the simplest form, and typically used to display time on our x-axis and a metric being measured on the y-axis. This type of chart is excellent for tracking sales or other metrics over months, quarters, or years.

Line and Stacked Column Chart

Line and stacked column charts combines a line chart with a stacked column chart, providing a dual perspective on our data. The line represents one metric, while the stacked column represents another. This type of chart can be useful for comparing the overall distribution of different categories along with the trend of a key metric.

For example, we can use a line and stacked column chart to display our year-to-date cumulative sales as a continuous series while showing the monthly contribution of each sales region.

Line and Clustered Column Chart

The line and clustered column chart is another hybrid visual that combines a line and clustered column charts. In this chart type, columns represent individual categories clustered together, while the line element shows the trend of a specific metric. For example, we can show a comparison of the previous year and current year monthly total sales as our clustered columns and the average sales for each year over time on our line axis.

Each of these chart types provides a unique way to visualize data, offering flexibility and depth to our analysis. Understanding when and how to use these different line charts can greatly enhance our ability to communicate data insights effectively.

Customizing Line Charts

Customizing line charts in Power BI allows us to create visually appealing and informative visuals that effectively communicate our data trends to our viewers. There are various ways and opportunities for out of the box thinking when it comes to our customizations. Here we will focus on some of the main customization options available across all of the line chart types.

Line Color and Styles

To make our lines more visually distinct we can leverage the properties found under the Line property group. These options let us define the color, style, and thickness of all the lines on our chart or for specific series.

Let’s explore these options to improve the Total & Average Sales Line and Clustered Column visual shown above.

We start by adjust our colors, we will keep a blue color theme for 2024 data and use a gray color theme for 2023 data. Under our Lines property setting we select our Average Sale 2024 in the Series drop down and select 60% lighter color of our dark blue in our Power BI theme, keeping it consistent and corresponding to the 2024 column.

Next, we select the Average Sale 2023 series in the Series dropdown and set the color to a gray. We select a color that is light enough to contrast with the dark blue bars, yet dark enough to contrast the light-colored background of the visual.

This slight change already improves our visual’s clarity, but we can go one step further. In addition to using different colors to differentiate our lines we can use different styles to further differentiate our series. Here, with the Average Sale 2023 series selected, under the Line options we set the Line style to Dashed and reduce the width to 2px.

Data Markers and Labels

In addition to setting different colors and styles for our lines, we can also set the data marker style or not show them at all.

Data markers make it easier to identify specific values and can help compare different values. For example, in the above visual we only show data labels for the Average Sale 2024 series. However, since we show the data markers for both series, we can present our user the current year’s average and its value, and then it can easily be compared to the previous year’s data marker.

Let’s further clarify our different series by setting the data marker for our Average Sale 2023 to a diamond shape and reduce its size to 4px. We select the series we want to modify from the drop down, and then select diamond in the shape dropdown and then reduce the size.

Adding data labels to our visuals provides our viewer additional context and precise information by displaying the value of each data point on the chart. We find the options to customize our data labels under the Data labels property group. Here, we find options to set the position of our data labels, display a title, customize the value displayed and how it is formatted, add additional detail to display another value, set the background of the label, and define whether the label should appear on a single line or multiple lines.

To help not clutter our Total & Average Sales visual we turn off the labels for the 2023 column and series. To do this we select each series in the Series dropdown and toggle off the Show for this series option.

Then we modify our data labels for our 2024 columns and Average Sale 2024 series. We start with our 2024 column data label and select it in the Series dropdown. Then we set the background of the data label to the same color as the column, set the value color to white, and under the options settings we select Outside end in the Position (column) dropdown.

Then we select our Average Sale 2024 in the Series dropdown, set the background to the same color as the line, the value color to a dark blue, and select Above in the Position (line) dropdown.

By customizing our line color, style, data markers and data labels our visual now more clearly differentiates between our 2024 and 2023 data through the use of different colors and styles. Clearly labeling our 2024 data points provide precise information for the current year data, while also being able to relatively compare this value to the previous year data.

Other Customizations

There are many other customization options available. There are other line chart specific options to explore including series labels and shading the area under the line series. Then there are also customization options that are common across many different visual types including formatting the x- and y-axis, the legend, gridline, tooltips, etc. For examples and an exploration of these common options visit Part 1 of this series.

Explore Power BI Core Visualizations: Part 1 – Bar and Column Charts

Chart your data journey! Transform data into insights with Power BI core visualizations.

By customizing our line charts, we are able to create compelling visuals that highlight key trends and insights in our data. By leveraging these different customization options, we can enhance the clarity and impact of our visuals.

Types of Area Charts

Area charts are a variation of line charts that fills the area beneath the line with color, emphasizing the magnitude of the values. They can be particularly useful for visualizing cumulative data, where the area fill highlights the overall value in addition to the trend.

Area Chart

A standard area chart fills the area below the series line making it easy to see both the trend and volume of the dataset over time. We can use this chart to display continuous data where the emphasis is on the total value represented by the area. For example, we can use an area chart to visualize our year-to-date running total sales for our dataset.

Stacked Area Chart

The Stacked Area Chart is an extension of the basic area chart, where multiple data categories are stacked on top of each other. Each category is represented by a different colored area, showing the contribution of each category to the total value. Similar to the stacked bar/column charts, this chart type is useful for visualizing the contribution of different categories to the whole.

For example, we will update our Running Total Sales to show each region’s contribution to the running total over time. By stacking the areas, it becomes clear how each region’s sales trend and compare to the others. This chart is effective when we need to show the relationship between multiple data series and their cumulative impact on a metric.

100% Stacked Area Chart

The 100% Stacked Area Chart is similar to the stacked area chart but shows the relative percent contribution of each category to the total. Each area is normalized to fill 100% of the y-axis, making it easy to see the proportional contribution of each category over time. This type of area chart is ideal for when we need to compare the relative size of different categories.

For example, with a 100% Stacked Area chart we can update our Running total sales visual to show how each sales region contribution to our annual total sales has changed throughout the year. This provides a different method to visualize and understand how each of our sales regions contribute to our total sale through time.

Each of these area chart types provides a unique way to visualize data, offering us flexibility and depth to our data analysis. Understanding when and how to use each different type can greatly enhance our ability to communicate data insights effectively.

Customizing Area Charts

Customizing our area charts allows us to ensure our charts are informative as well as visually appealing and effectively communicate our data insights. Let’s explore several customization options to enhance our area charts.

Fill Colors and Transparency

To make sure our area charts are visually consistent with other visuals in our report we can set the color of each series and adjust the transparency to get the visual just right. For example, a common theme within our Sales report is to visualize our regions using a green color palette.

So, when creating the Running Sales stacked area chart above, we set the color for each sales region explicitly to ensure consistency.

Power BI provides us two property groups to help customize the line and fill color of our area charts. The first is setting the line color, here we can select each series we want to customize from the Series dropdown and apply the desired formatting. The other is the Shade area properties that we can customize. By default, the Match line color is toggled on however, if required we can toggle this option off and set the area color to a specific color.

Data Labels and Markers

By using data labels and markers we can enhance the clarity of our area charts by displaying the exact values and providing our viewers the information they require at just a glance. To label our area charts we can utilize data and series labels and for our stacked area charts we also have a total label property. Let’s explore each of these on Running Total Sales stacked area chart.

We will start by formatting the data labels. The data labels will be formatted the same for all our regional sales series. We set the color to a dark green color, and then under Options we set the position to inside center. This will show each regional contribution to the total cumulative sales and display it in the center of the shaded area.

Next, we toggle on the Total labels to show the cumulative total of our sales through time. To distinguish this value from the data labels, we set the background of the label to a darker green, transparency to 0%, and then set the color of our values to white.

Lastly, to better clarify what each area on our chart represents, we turn on the series labels. We position the series labels on the right side of the chart and format the series colors to correspond with the line/area color for each series.

In addition to using data labels, we can identify each data point using markers. We can also set different marker for each data series to continue to provide our viewers visual cues to differentiate our series. Here, we set the Asia sales regions to use circle markers, the Europe sales region to use diamond markers, and the United States sales region to use square markers.

Other Customizations

Like our line charts there are many other customization options available then we explored here. Check out Part 1 of this series for details on customizing our visual’s x- and y-axis, legend, gridlines, tooltips, and more.

Explore Power BI Core Visualizations: Part 1 – Bar and Column Charts

Chart your data journey! Transform data into insights with Power BI core visualizations.

By customizing our area chart, we can create a compelling visual narrative that highlight the key data trends in our data. To maximize our visual’s clarity when it includes multiple data series (e.g. stacked area charts) it’s important to use clearly distinguished colors and to avoid displaying areas that excessively overlap. By effectively leveraging our customization options, we can enhance our visuals ensuring our data tells a compelling story.

Ribbon Chart

A ribbon chart is a unique and powerful visual that showcases rankings of different categories. These charts highlight both the rank of the categories within and across different groupings. A ribbon chart is particularly effective in scenarios where we want to understand the flow and change of ranks over different groupings, such as the totals sales of product categories across our different sales regions.

The customization options available for us for our ribbon charts are similar to the options we have already explored in the previous sections, so we won’t explore then again here. For example, in the above visual we leveraged total labels, data labels, and specific colors for our columns.

Ribbon charts can be a powerful tool for visualizing ranking and trends, providing valuable insights into the relative performance of different categories. By effectively utilizing ribbon charts in our reports, we can provide our viewers an informative and unique visual that drives deeper insights.

Advanced Techniques and Customizations

Enhancing our Line and Area charts with advanced techniques and customizations can help us elevate the clarity and impact of our visuals. Let’s explore some examples.

Plotting Year over Year Comparisons

Year-over-year comparisons can help improve our understanding of trends and annual patterns in our data. Using a line chart allows us to clearly visualize these patterns.

Here is the plot we will be creating to provide year-over-year insights to our viewers.

First, we ensure our data includes the proper time dimension, in this example we use the total sales for each month. We start building the visual by adding a Total Sales measure to the y-axis of our line chart and our Month time dimension to the x-axis.

We can see now that our plot displays the Total Sales for each month. To separate and distinguish the monthly total sales for each year we add our Year time dimension to the chart’s Legend field.

The line chart now shows the monthly Total Sales for each year within our dataset. To enhance the clarity of this visual and highlight the current year we continue to format this visual.

To start, we use the Line properties for each series to improve the visual distinction between the previous years and the current year. We set the color of the 2022 series to a light blue and set the line style to dotted, 2023 we set the color to a slightly darker blue and the line style to dotted. Lastly, we set 2024 to a dark blue, keep the line style as solid and increase the width to 4px.

Then we move to adding line markers to give our viewers more precise information on each monthly value. For all of the data series we set the marker type to a circle. For the 2022 and 2023 series the marker size is 3px and then to better highlight the 2024 values we increase the size to 5px.

Lastly, to display the specific monthly values for only 2024 we enable Data labels and then for the 2022 and 2023 we toggle Off the Show for this series option. To ensure the data labels for 2024 sales stands out and are easy to read we set the background of the label to the same color as the series line and set the transparency to 0%, and the Value color to white.

These formatting options gives us our final visual.

By using this approach, we can quickly compare our year-over-year total sales while highlighting the data our viewers are most interested in.

Conditional Formatting Based on Data Thresholds

In the above year-over-year line chart we used line style and color to distinguish our data series and highlight data. Conditional formatting provides us another excellent way to draw attention to key data points. By using conditional formatting, we can automatically change the color of various chart components based on defined thresholds.

The goal with this plot is to highlight for our viewers when the current year’s average monthly sales are above or below the previous year’s average monthly sales. Here is the final visual.

To create this visual we have two measures, the first calculates the monthly average sales for the current year and the second calculates the monthly average sales for the previous year.

In addition to these two measures, we create three new measures to assist with labeling and formatting the specific data points.

Annual Comparision = [Average Sale Current Year] - [Average Sale Previous Year]

Annual Comparison CY < LY Label = 
If([Annual Comparision] < 0,[Average Sale Current Year])

Annual Comparison CY > LY Label = 
If([Annual Comparision] > 0,[Average Sale Current Year])

The first measure provides us a comparison of the current year sales and the previous year sales. We then use this to create the label measures.

Once we have our measures, we start constructing our visual by adding a Line and Clustered Column chart to our report. To create this visual we use the Line and Cluster chart, this allows us to add multiple series to the Line y-axis field.

We start by adding both of the average monthly sales measures to the line y-axis data field of the visual and our MonthName time dimension field to the x-axis data field. Then format the lines in a similar fashion that we did in the above example.

We then add the Annual Comparison CY < LY Label and Comparison CY > LY Label measures to the visual. On this visual the ordering of the data series is important. The order of the measures in the Line y-axis data field is Average Sale Current Year, Average Sale Previous Year, Annual Comparison CY < LY, and lastly Annual Comparison CY > LY.

Now that all of the series we need are added to our visual we need to enhance it with our customization and formatting.

Since the only lines we want on the visual are the Average Sale Current Year and Average Sales Previous Year, we start by setting the Line width of the Annual Comparison measures to 0px and the color to the same color as the visual background color so the dot for these series cannot be seen in the legend.

We then move to formatting the markers for the Annual Comparison series. For the Annual Comparison CY > LY Label series we set the marker size to 6px and color to green, and for the Annual Comparison CY < LY Label series we set the marker size to 6px and the color to red.

Lastly, for the data labels that we will apply to the Annual Comparison series we add another measure that we can use to show the percent difference between the previous year average sales and the current year average sales.

Annual Comparison Percent Difference = 

VAR _percentDiff = ([Average Sale Current Year]-[Average Sale Previous Year])/(([Average Sale Current Year]+[Average Sale Previous Year])/2)

RETURN

IF(_percentDiff <0, "▼ " & FORMAT(ABS(_percentDiff), "0.0%"), "▲ " & FORMAT(_percentDiff, "0.0%"))

We toggle on our Data labels property, and then toggle off Show for this series for our Average Sales Current Year and Average Sales Previous Year series.

Then for the Annual Comparison CY < LY Label series under the Options property settings we set the Position (line) setting to Under, set the Value field to the Annual Comparision Percent Difference measure, the color of the value to white, and the background of the label to the same red color as the marker with a transparency of 0 %.

We format the Annual Comparison CY > LY Label series in a similar fashion, however, we set the Position (line) to Above and the background color to the same green we used for the marker.

Lastly, we rename the Annual Comparison measures for the visual and clear the series name so like the marker in the legend the series name is not shown.

Using conditional formatting helps us make our visuals more informative by visually emphasizing data points that meet certain criteria.

Visualizing Thresholds with Area Charts

Visualizing threshold or ranges with area charts can be a useful and powerful way to compare actual performance against a benchmark or target. We can use this approach to compare our historic actual sales to the forecasted value and visualize the forecasted data projected into the future.

This is the visual we will be creating.

We start to build this visual by adding an Area chart to our report. Then a time dimension hierarchy to the x-axis to plot the values for each year and month and add our Actuals Sales and Forecast measures to the plot.

We then format these series. For our Actual Sales series we set the line color to green with a width of 4px, we toggle off the Shade area setting and set the markers to circles with a dark green color. Then for the Forecast series we set the line color to gray with a width of 3px, we toggle off the Shade area setting, and toggle off the markers.

Then we add our Forecast Upper Limit and Forecast Lower Limit measures to the visual. The order of the series is essential to have the visual display correctly. The order should be Sales Actual, Forecast, Forecast Upper Limit, and lastly Forecast Lower Limit.

After adding the Upper and Lower Limit series we must format them to display just the way we want. For both the Forecast Upper and Lower Limit series we set the Line color to a light gray, and toggle off the Show for this series option for both the Line and Markers properties. Then we move to the Shade area properties, and for the Forecast Lower Limit series we toggle off Match line color and set the share area color to the same color as our visual background with the area transparency set to 0%.

Lastly, we ensure the visual is just right by setting the x-axis and y-axis titles and toggling off the built-in Legend. In place of the built-in legend, we construct a custom Legend to better identify each component on the visual.

Once all our customizations are complete, we have a visual packed with information that allows our report viewers to easily compare our historic actual sales to the forecasted values and view the forecasted sales projected into the future.

Wrapping Up

In this second part of this series on Power BI core visuals, we explored the versatility and power of Line and Area Charts along with their variations and combinations. Line Charts help us track changes over time with clarity, making them a perfect tool for trend analysis and performance monitoring. Area Charts add a layer of visual emphasis on cumulative data allowing for a visual understanding of volumes in addition to trends. The hybrid charts, combing Line and Column Charts offer a multifaceted view of our data allowing us to provide our report viewers comprehensive insights.

By understanding the strengths and weaknesses of these charts we are able to identify opportunities to leverage them to create more informative and visually appealing reports in Power BI.

Stay tuned for Part 3, where we will delve into Pie, Donut, and Treemap visuals. We explore how they can help us provide a quick snapshot of proportional data, discuss their limitations, and where they fit into our visualization toolkit.

Thank you for reading! Stay curious, and until next time, happy learning.

If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.