Power BI

Creating Dynamic Bar Chart in Power BI

Table of Contents

Data Analysis Expressions (DAX) in Power BI is a potent language designed for data manipulation and presentation, enabling analysts to craft stories from their data.

[If you are a beginner, we highly recommend you to get familiar with the DAX Studio first before moving ahead]

DAX is much more then creating calculated columns and calculated measures. Today, we’re exploring how DAX can turn a straightforward bar chart into an interactive canvas in Power BI. 

This tutorial aims to demonstrate the enhancement of a bar chart’s visualization capabilities using DAX.

Overview

The initial section of this blog focuses on developing calculated measures to address data gaps. Subsequently, these measures are leveraged in the latter half to construct a dynamic bar chart, capable of displaying varying values on a singular graphical representation contingent upon user interaction. This approach underscores the versatility of DAX in enriching data visualisations, making them more interactive and informative for decision-making processes.

Understanding the Data Set

The example data model for this illustration comprises two primary tables: the Sales_Table and the Product_Table.

  • The Product_Table serves as a dimension table, holding product-related data
  • Sales_Table, a fact table, records transactional data.

Let’s now transition into the tutorial phase. By following the steps outlined below, you’ll learn how to utilize DAX within Power BI to create dynamic bar charts that can adapt to user selections. Each step is designed to guide you through the process, ensuring that you can not only replicate these techniques but also understand the principles behind them. This hands-on approach aims to equip you with the skills needed to enhance your data visualizations, making them more engaging and insightful.

Tutorial: Creating Dynamic Bar Chart in Power BI

To Get Started:

1. Create a table All Measures for measuring values.

2. Create a new measure Revenue according to the following DAX expression:

3. Create a new measure COGS according to the following DAX expression:

4. Create a new measure Profit according to the following DAX expression:

After implementing the necessary measures using DAX, our data model has evolved to include these enhancements. This evolution reflects not just the addition of new calculated measures, but also a refined structure that supports more dynamic and insightful data visualisations.

To implement conditional formatting in a bar chart using DAX, we’ll start by setting up a table that facilitates user selection, followed by specifying the columns/measures for dynamic switching. Here’s how to proceed:

First we create a table to get selection from user (Steps 1-3) and then we enter the Column/Measure names that will be used as dynamic switching options (Steps 4-6)

1. Initiate Table Creation: Click on Home Tab in the Power BI Ribbon Menu.

2. Generate Table: Click on Enter Data in the External Data section to create a new table.

3. Configure Selection Column: In the new table window, rename Column1 to “Selection”. This column will hold our dynamic switching options.

4. Populate Selection Options: In the “Selection” column, input “COGS” as the first value to represent Cost of Goods Sold.

5. Add More Options: Follow by entering “Profit” as the second option, allowing users to switch to viewing profit metrics.

6. Complete the Selections: Finally, add “Revenue” as another option in the cell, enabling a comprehensive view of financial metrics.

7. Load the Table: After entering the values, click “Load” to add this table to your Power BI model. This action solidifies your setup for user selections, setting the stage for dynamic data visualisation.

Creating SWITCH Function in DAX

The SWITCH function in DAX plays a crucial role in conditional formatting, enabling the visualisation to adapt based on user input. It facilitates seamless switching between different values, enhancing the interactivity and flexibility of the report.

8. Initiate Measure Creation: Begin by clicking on “New Measure” in your Power BI report. This option is typically found in the Modeling tab or directly in the Fields pane.

9. Enter the DAX Expression: In the formula bar that appears, you’re set to type in the DAX expression using the SWITCH function. This measure will define how your bar chart responds to selections made by the user, essentially controlling the conditional formatting based on the “Selection” table you created earlier.

We incorporated the SWITCH function as it allows user to choose between different options and returns the value according to the input selected. Now, we will create a SLICER to further enhance the interactivity of our report as it will allow us to easily switch between options; Finally we will then create a dynamic bar chart.

Creating a SLICER Function in DAX

10. Add a Slicer: Navigate to the Visualisation pane and select the Slicer visual.

11. Configure the Slicer: Drag the “Selection” column from your table into the Values area of the slicer. This action sets up your slicer with the options “COGS”, “Profit”, and “Revenue”, allowing users to choose the metric they wish to view.

Building a Dynamic Bar Chart in Power BI

12. Insert a Bar Chart: In the Visualization pane, click on the Bar Chart icon to add a new bar chart to your report.

13. Set Up the Axis: Drag the “Product” field from the Product_Table to the Axis field of the bar chart. This step defines the products as the categories along the x-axis of your bar chart.

14. Add the Dynamic Measure: Drag the newly created measure (using the SWITCH function) into the Values area of the bar chart. This measure will dynamically update based on the user’s selection in the slicer, altering the metric displayed on the bar chart accordingly.

Interact with the values in the slicer to see how it conditionally influences the data presented in the bar chart. This practical application underscores the versatility and power of DAX in customising visualisations according to user preferences.

By following these steps, you establish a highly interactive visualisation setup where users can select the financial metric they are interested in, and the bar chart will dynamically update to display the chosen metric across different products. This not only makes your Power BI report more engaging but also provides users with a flexible tool to explore the data from multiple perspectives.

Ending Notes

The process of creating dynamic bar charts in Power BI illustrates the expansive capabilities of DAX beyond just formulating calculated columns and measures. It stands as an essential instrument in enhancing the interactivity and adaptability of your data visualizations. Through this example, we’ve explored how DAX can revolutionize a straightforward bar chart into a dynamic visualization that adjusts to user interactions. This enhancement not only enriches the analytical experience but also provides deeper, more accessible insights into the data.

Power BI DAX Essentials Course

For data analysts keen on mastering the art of creating dynamic bar charts in Power BI and harnessing the full potential of DAX, further education through specialized training is invaluable.

Power BI Training by AMZ offers specialised Power BI courses including a 2-day DAX Essentials course in Australia.

We deliver all our trainings in partnership with Dynamic Web Training and have a state-of-the-art classroom facility across major cities like Sydney, Melbourne and Brisbane. We also deliver live online trainings.

Our Power BI courses including the DAX Essentials course aims to broaden your skills and understanding of Power BI, enabling you to craft sophisticated, interactive reports that effectively communicate your data’s story.

For more information, please email us at: info@amzconsulting.com.au 

or 

fill this form and our representative will contact you. 

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *