{"id":80470,"date":"2018-08-21T13:52:16","date_gmt":"2018-08-21T13:52:16","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=80470"},"modified":"2022-04-24T20:38:37","modified_gmt":"2022-04-24T20:38:37","slug":"power-bi-introduction-building-reports-in-power-bi-desktop-part-7","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-bi-introduction-building-reports-in-power-bi-desktop-part-7\/","title":{"rendered":"Power BI Introduction: Building Reports in Power BI Desktop \u2014 Part 7"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Power BI Introduction: Tour of Power BI \u2014 Part 1<\/a><\/li>\n
  2. Power BI Introduction: Working with Power BI Desktop \u2014 Part 2<\/a><\/li>\n
  3. Power BI Introduction: Working with R Scripts in Power BI Desktop \u2014 Part 3<\/a><\/li>\n
  4. Power BI Introduction: Working with Parameters in Power BI Desktop \u2014 Part 4<\/a><\/li>\n
  5. Power BI Introduction:\u00a0Working with SQL Server data in Power BI Desktop \u2014 Part 5<\/a><\/li>\n
  6. Power BI Introduction:\u00a0Power Query M Formula Language in Power BI Desktop \u2014 Part 6<\/a><\/li>\n
  7. Power BI Introduction: Building Reports in Power BI Desktop \u2014 Part 7<\/a><\/li>\n
  8. Power BI Introduction: Publishing Reports to the Power BI Service \u2014 Part 8<\/a><\/li>\n
  9. Power BI Introduction: Visualizing SQL Server Audit Data \u2014 Part 9<\/a><\/li>\n<\/ol>\n\n

    Until now, this series has focused primarily on building datasets in Power BI Desktop, with the understanding that the data would ultimately be used to create visualizations that provide insights into the underlying information. The visualizations would be included in one or more reports that could then be published to the Power BI service for wider distribution.<\/p>\n

    This article switches focus from building datasets to creating reports that rely on those datasets, using the tools within Power BI Desktop for visualizing the data. The article offers several examples of how to add visualizations to a report to provide different perspectives of the data. The visualizations are based on a single dataset, created from data in the AdventureWorks2017<\/strong> sample database in SQL Server 2017.<\/p>\n

    If you plan to try out the examples for yourself, you can use the following T-SQL statement to add a dataset to Power BI Desktop:<\/p>\n

    SELECT h.SalesPersonID AS RepID,\r\n  CONCAT(p.LastName, ', ', p.FirstName) AS FullName, \r\n  a.City, sp.Name AS StateProvince, cr.Name AS Country, \r\n  h.OrderDate, h.SubTotal\r\nFROM Sales.SalesOrderHeader h \r\n  INNER JOIN Person.Person p\r\n    ON h.SalesPersonID = p.BusinessEntityID\r\n  INNER JOIN Person.BusinessEntityAddress ba\r\n    ON p.BusinessEntityID = ba.BusinessEntityID\r\n  INNER JOIN Person.Address a\r\n    ON ba.AddressID = a.AddressID\r\n  INNER JOIN Person.StateProvince sp\r\n    ON a.StateProvinceID = sp.StateProvinceID\r\n  INNER JOIN Person.CountryRegion cr\r\n    ON sp.CountryRegionCode = cr.CountryRegionCode\r\nWHERE h.SalesPersonID IS NOT NULL\r\nORDER BY FullName ASC;<\/pre>\n
    For information about using a T-SQL query to create a dataset, refer to Part 5<\/a> of this series.<\/p>\n
    After you import the data into Power BI Desktop, change the name of the dataset to Sales,<\/em> update the format of the OrderDate<\/em> column to show only the dates (not the times), and update the SubTotal<\/em> column to show whole number currency values.<\/p>\n
    To update for the OrderDate<\/em> column, select the dataset in Data<\/em> view and then select the OrderDate<\/em> column. On the Modeling<\/em> ribbon, select Date<\/em> from the Date type<\/em> drop-down list. Next, click the Format <\/em>drop-down list, point to Date Time,<\/em> and click the format 2001-03-14 (yyyy-MM-dd)<\/em>.<\/p>\n
    To update the SubTotal<\/em> column, select the column in Data<\/em> view and then select Whole Number<\/em> from the Data type<\/em> drop-down list. You should receive a warning message about the loss of data precision. Click Yes<\/em> to continue. In the Currency format<\/em> drop-down list, click $ English (United States)<\/em>. In the Decimal places<\/em> box, set the number of decimal places to 0<\/em>.<\/p>\n
    Getting started with Power BI Desktop Reports<\/h2>\n
    With the dataset in place, you\u2019re ready to start adding visualizations to the report. But before you do, save the file if you haven\u2019t already done so. The file provides the structure necessary for publishing the report\u2014along with its visualizations and datasets\u2014to the Power BI service. Each file is associated with a single report made up of one or more pages, with each page containing the actual visualizations.<\/p>\n
    To add a visualization to a report, go to Report<\/em> view, where you\u2019re presented with a design surface and the Visualizations<\/em> and Fields<\/em> panes, as shown in the following figure. The design surface contains the report pages, with Page 1<\/em> already added to the report.<\/p>\n
    <\/p>\n
    The Visualizations<\/em> pane provides the tools necessary for adding and configuring the visualizations. The Fields<\/em> pane includes a list of the defined datasets, with access to each dataset column. The above figure indicates that the Sales<\/em> dataset is the only visible dataset included in this report file. (In Power BI Desktop, you can configure a dataset to be hidden in Report<\/em> view.)<\/p>\n
    Most of the tasks you carry out in Report<\/em> view are point-and-click or drag-and-drop operations. For example, to add a visualization to a report page, you simply click the visualization\u2019s button at the top of the Visualizations<\/em> pane. Power BI Desktop will add the visualization to the design surface, where you can drag it to a different position or resize its window. You can then specify what data to add to the visualization by selecting columns from the Fields<\/em> pane.<\/p>\n
    When a visualization is selected on the design surface, Power BI Desktop updates the Visualizations<\/em> pane to include configuration options specific to that visualization. For example, the following figure shows the Visualizations<\/em> pane with a Matrix<\/em> visual selected on the design surface. In this case, the Country<\/em> and FullName<\/em> columns have been added to the Rows <\/em>section and the OrderDate<\/em> column has been added to the Columns<\/em> section, with the date hierarchy expanded. (We\u2019ll be getting into the details of how to add columns in just a bit.)<\/p>\n
    <\/p>\n
    The top of the Visualizations<\/em> pane contains the icon buttons you use to add visualizations to a report. The rest of the pane is specific to configurating the selected visualization. This part of the pane is divided into three tabs: Fields, Format,<\/em> and Analytics<\/em>. In the above figure, the Fields<\/em> tab is selected, as indicated by the red circle. The options on this tab are used to apply data to the visualization.<\/p>\n
    The second tab, Format,<\/em> provides options for configuring how the selected visualization appears. The options are separated into categories, which are specific to the selected visualization. The following figure shows the Format<\/em> categories for the Matrix<\/em> visual.<\/p>\n
    <\/p>\n
    The Analytics<\/em> tab lets you add dynamic reference lines to certain types of visualizations. Later in the article, we\u2019ll be discussing this tab in more detail.<\/p>\n
    Creating a Matrix Visual<\/h2>\n
    The best way to understand how to work with visualizations is to set them up for yourself. In this way, you get hands-on experience with the tools, while seeing how all the pieces fit together. For the first example, you\u2019ll add and configure a Matrix<\/em> visual and two Slicer<\/em> visuals. The following figure shows what they\u2019ll look like after you\u2019ve set them up. The visualization on the left is the Matrix<\/em> visual and the two on the right are the Slicer<\/em> visuals. The Matrix<\/em> visual is selected in the figure, as indicated by the menu across the top of the visualization and the frame that surrounds it.<\/p>\n
    <\/p>\n
    The Matrix<\/em> visual aggregates measure data across both columns and rows, while supporting extensive drill-down capabilities, depending on the data and how you\u2019ve configured the matrix.<\/p>\n
    To add the Matrix shown in the previous figure, take the following steps:<\/p>\n
      \n
    1. Click the Matrix<\/em> button in the top section of the Visualizations<\/em> pane. This adds a placeholder object to the design surface, which is already selected.<\/li>\n
    2. Drag the Country<\/em> column from the Fields<\/em> pane to the Rows<\/em> section on the Visualizations<\/em> pane.<\/li>\n
    3. Drag the FullName<\/em> column from the Fields<\/em> pane to the Rows<\/em> section on the Visualizations<\/em> pane, directly beneath the Country<\/em> column. By adding the FullName<\/em> column after the Country<\/em> column, you make it possible for users to drill down into the country to view data about the sales reps in that country.<\/li>\n
    4. Drag the OrderDate <\/em>column from the Fields<\/em> pane to the Columns<\/em> section on the Visualizations<\/em> pane. Because the OrderDate <\/em>column contains date values, Power BI Desktop automatically defines hierarchical levels that can be used to drill down into the data based on dates.<\/li>\n
    5. Drag the SubTotal <\/em>column from the Fields<\/em> pane to the Values <\/em>section on the Visualizations<\/em> pane. The SubTotal <\/em>column provides the measure on which the aggregations are based.<\/li>\n<\/ol>\n
      The Fields <\/em>tab on the Visualizations<\/em> pane should now look like the one in the following figure.<\/p>\n
      <\/p>\n
      After you add the data you need, you can configure the visual\u2019s settings on the Format<\/em> tab to ensure it looks just the way you want, and the data can be easily understood. To make your matrix look similar to the one shown in the above figure, take the following steps:<\/p>\n
        \n
      1. Expand the Matrix style<\/em> section and select Minimal<\/em> from the Style<\/em> drop-down list.<\/li>\n
      2. Expand the Grid<\/em> section and set the Vert grid<\/em> option to On,<\/em> and then select the lightest yellow color for the Vert grid color<\/em> option.<\/li>\n
      3. Expand the Column headers<\/em> section and set the Text size <\/em>option to 12<\/em>.<\/li>\n
      4. Expand the Row headers<\/em> section and set the Text size <\/em>option to 12<\/em>.<\/li>\n
      5. Expand the Values<\/em> section and set the Text size <\/em>option to 10<\/em>.<\/li>\n
      6. Expand the Subtotals <\/em>section and set the Text size <\/em>option to 10<\/em>.<\/li>\n
      7. Expand the Grand total <\/em>section and set the Text size <\/em>option to 10<\/em>.<\/li>\n
      8. Expand the Title<\/em> section and set the Title <\/em>option to On<\/em>. In the Title Text<\/em> box, type Sales by Country,<\/em> and set the Font color<\/em> option to the darkest aquamarine. In the Alignment<\/em> subsection, click the Center<\/em> option, and then set the Text size<\/em> option to 17<\/em>.<\/li>\n
      9. In the Border<\/em> section, set the option to On<\/em>.<\/li>\n<\/ol>\n
        Your Matrix<\/em> visual should now be in fairly good shape. Going forward, I\u2019ll leave it up to you to decide how to configure the settings on the Format<\/em> tab, based on your own preferences. The main thing to keep in mind when formatting a visualization is to make sure it\u2019s selected on the design surface.<\/p>\n
        When you select a Matrix <\/em>visual on the design surface, drop-down list is available for choosing whether drill-down operations are based on columns or rows, as shown in the following figure.<\/p>\n
        <\/p>\n
        To test this out, select Columns<\/em> from the Drill on<\/em> drop-down list, and then click the button Expand all down one level in the hierarchy<\/em> (third button to the right of the drop-down list). When you click the button, the matrix provides a breakdown of the aggregated data based on quarters as well as years, as shown in the following figure.<\/p>\n
        <\/p>\n
        To return to the previous view, click the Drill Up<\/em> button just to the right of the Drill on<\/em> drop-down list. Next, click the button Click to turn on Drill Down<\/em> near the top right corner to turn on drill-down capabilities directly within the matrix. When this button is selected, its shading is reversed.<\/p>\n
        With drill-down enabled, click the 2013<\/em> column header. The matrix will now display data specific to that year, as shown in the following figure.<\/p>\n
        <\/p>\n
        As already noted, Power BI Desktop automatically creates a hierarchy based on the date values, with years at the top, then quarters, next months, and finally days. You can drill down into all levels within a Matrix<\/em> visual. For example, to drill into the first quarter, click the Qtr 1<\/em> column header, which allows you to view sales data at the month level, as shown in the following figure.<\/p>\n
        <\/p>\n
        If you select the Rows <\/em>option in the Drill on<\/em> drop-down list, instead of the Columns<\/em> option, you will see different results when drilling into the data. For example, if you click the name of a country in the Country<\/em> column, the matrix will display a list of sales reps for that country, along with their sales totals. Give it a try and see how to goes.<\/p>\n
        You can also view a visualization in Focus mode, which enlarges the visualization so that it uses the entire design surface. To view a visual in focus mode, click the Focus mode<\/em> button near the top right corner. Your Matrix<\/em> visual should now look similar to the one in the following figure. To return back to the regular view of the design surface, click the Back to Report<\/em> button.<\/p>\n
        <\/p>\n
        You can also add slicers to your reports that provide a way for users to filter the data within the visualization. For this exercise, you will add two slicers, one for countries and one for years.<\/p>\n
        To add the Country<\/em> slicer, verify that no visualizations are selected on the design surface, and then click the Slicer<\/em> button at the top of the Visualizations<\/em> pane, as shown in the following figure. The Slicer<\/em> button is outlined in yellow to indicate that it is the selected visual type.<\/p>\n
        <\/p>\n
        Next, drag the Country<\/em> column from the Fields<\/em> pane to the Field<\/em> section in the Visualizations<\/em> pane. You can then move and resize the slicer, as well as apply formatting. Power BI Desktop will automatically link the slicer to any related visualizations on that page. You might also need to change the slicer type from a dropdown list to a regular list of items so the slicer is more readable. To change the type, click the down arrow within the slicer visual itself, near the top right corner, and then select List<\/em>.<\/p>\n
        To add the Year <\/em>slicer, take the same steps as you did for the Country<\/em> slicer, only this time, add the OrderDate<\/em> column. Next, click the column\u2019s down arrow (on the Visualizations<\/em> pane), and click Date Hierarchy<\/em>. Power BI Desktop adds only the Year <\/em>level to the slicer. Again, you might need to change the slicer type from Dropdown<\/em> to List<\/em>.<\/p>\n
        When formatting the two slicers, you can add a Select All<\/em> value to the list of available values. To add the value, go to the Format<\/em> tab on the Visualizations<\/em> pane, expand the Selection Controls<\/em> category, and set the Show \u201cSelect All\u201d<\/em> option to On<\/em>.<\/p>\n
        To test the new slicers, select France, Germany,<\/em> and United Kingdom<\/em> in the Country<\/em> slicer, and select 2013<\/em> and 2014<\/em> in the Year<\/em> slicer. To select multiple values in a slicer, select the first value, press Control<\/em>, and select the remaining values. Your visualizations should now look like those shown in the following figure.<\/p>\n
        <\/p>\n
        Once you\u2019ve mastered how to add and configure a Matrix <\/em>visual, you\u2019ll find that many of the concepts introduced here will apply to other visualizations. Just be certain to save your report file regularly to ensure that none of your changes get lost.<\/p>\n
        Creating Table and Card Visuals<\/h2>\n
        When you add columns to a visualization, Power BI Desktop will often summarize the data to provide a big-picture perspective of the information. You saw this with the Matrix<\/em> visual in the previous section, in which the SubTotal<\/em> column (the measure) was aggregated across the Country<\/em> and OrderDate<\/em> columns to provide subtotals for each discrete group.<\/p>\n
        In some cases, you might not want the data automatically aggregated. For example, you might want to add a Table<\/em> visual that lists individual Canadian sales only. To add this table, click the Plus<\/em> button at the bottom of the design surface to insert a second page. Next, click the Table<\/em> button on the Visualizations<\/em> pane, and then add the RepID, FullName, OrderDate,<\/em> and SubTotal<\/em> columns to the Values<\/em> section of the Visualizations<\/em> tab.<\/p>\n
        Power BI Desktop will automatically try to summarize the RepID<\/em> and SubTotal<\/em> columns because they contain numerical values, using the Count<\/em> and Sum<\/em> aggregate functions, respectively. For each of these two columns, click the column\u2019s down arrow (in the Visualizations<\/em> pane), and select Don\u2019t summarize,<\/em> as shown in the following figure. Power BI Desktop will update the data to include individual rows.<\/p>\n
        <\/p>\n
        When you add a date column to the Table<\/em> visual, Power BI Desktop automatically breaks the dates into hierarchical levels, which results in four columns (Year, Quarter, Month,<\/em> and Day<\/em>). If you want a single date column instead, you must reset the OrderDate<\/em> column. In the Visualizations<\/em> pane, click the column\u2019s down-arrow and select OrderDate<\/em>. Power BI Desktop will update the table so the entire OrderDate<\/em> value is listed in a single column.<\/p>\n
        The last step is to filter out all rows except the Canadian sales. To filter the data, drag the Country<\/em> column from the Fields<\/em> pane to the Visual level filters<\/em> subsection in the Filters<\/em> section of the Visualizations<\/em> pane. Expand the Country<\/em> section if not already expanded and select the Canada<\/em> checkbox, as shown in the following figure.<\/p>\n
        <\/p>\n
        Once you\u2019ve set up the data, position and resize the Table<\/em> visual and apply the necessary formatting. Your table should now look similar to the one shown in the following figure but formatted according to your preferences.<\/p>\n
        <\/p>\n
        The figure also shows a second visual, which is a Multi-row card.<\/em> In this case, the card displays the name of each Canadian sales rep, along with the total sales for that rep.<\/p>\n
        To add this card, verify that the Table<\/em> visual is not selected and then click the Multi-row card<\/em> button on the Visualizations<\/em> pane. Next, drag the FullName<\/em> column from the Fields<\/em> pane to the Fields<\/em> section of the Visualizations<\/em> pane, and then do the same for the SubTotal <\/em>column. Finally, set up the same filter you set up for the Table<\/em> visual so that only Canadian sales reps are listed. Position, resize, and format the card as necessary, and be sure to save the report file.<\/p>\n
        Creating Pie, Donut, and Treemap Charts<\/h2>\n
        In the next example, you\u2019ll add a page to the report and then add three visuals: Pie chart, Donut chart, <\/em>and Treemap<\/em>. The visualizations will display sales information for all countries except the United States. Although you\u2019ll be creating three different types of visualizations, you\u2019ll configure them exactly the same.<\/p>\n
        To add the Pie chart<\/em> visual, take the following steps:<\/p>\n
          \n
        1. Click the Plus <\/em>button to add a new page, and then click the Pie chart<\/em> button on the Visualizations <\/em>pane.<\/li>\n
        2. Drag the Country <\/em>column from the Fields<\/em> pane to the Details<\/em> section of the Visualizations<\/em> pane.<\/li>\n
        3. Drag the Subtotal <\/em>column from the Fields<\/em> pane to the Values<\/em> section of the Visualizations<\/em> pane.<\/li>\n
        4. Expand the Country<\/em> column in the Filters<\/em> section and select all countries except the US.<\/li>\n
        5. Position, resize, and format the visualization as necessary.<\/li>\n<\/ol>\n
          Once you have the first visualization exactly as you want to, you can create the next one, using the following steps:<\/p>\n
            \n
          1. Ensure that the Pie chart<\/em> visual is selected.<\/li>\n
          2. Click Copy<\/em> on the Home<\/em> ribbon, and then click Paste<\/em>. A copy of the original visualization is added to the design surface.<\/li>\n
          3. Drag the copied visual to its new position.<\/li>\n
          4. Click the Donut chart<\/em> button on the Visualizations<\/em> pane.<\/li>\n<\/ol>\n
            When you click one of the visualization buttons and a visualization is already selected, Power BI Desktop tries to apply the newly selected visual to the original configuration. This means that the visuals need to compatible with each other for this to work, like the two you just added.<\/p>\n
            The next step is to add the Treemap <\/em>visual, taking the same approach as with the Donut chart<\/em> visual. Copy and paste one of the other visuals and then click the Treemap <\/em>button at the top of the Visualizations <\/em>pane. When you\u2019re finished, you should end up with three visuals similar to those shown in the following figure.<\/p>\n
            <\/p>\n
            The visuals are positioned in the order they were created: Pie chart, Donut chart, <\/em>and Treemap<\/em>. Normally, you would not add such similar visuals to the same page unless you had a compelling reason, but what we\u2019ve done here demonstrates how easy it is to copy a visualization and change it to a different type.<\/p>\n
            In the figure above, the Treemap<\/em> visual also shows a pop-up message, which appears when you hover over a section of the visual. In this case, the pop-up is showing the total sales for the United Kingdom, but you can get information for any category on any of the three visualizations. In fact, most visualizations in Power BI Desktop support this feature.<\/p>\n
            Creating a Clustered Column Chart<\/h2>\n
            The next visualization you will add is a Clustered column chart<\/em> that shows US sales information for each sales rep with over $1 million in annual sales. The following steps walk you through the process of adding the visualization:<\/p>\n
              \n
            1. Click the Plus<\/em> button to add a new page to the report, and then click the Clustered column chart<\/em> button on the Visualizations<\/em> pane.<\/li>\n
            2. Drag the FullName <\/em>column from the Fields<\/em> pane to the Axis<\/em> section of the Visualizations<\/em> pane. The axis provides the foundation on which the chart is built.<\/li>\n
            3. Drag the OrderDate <\/em>column from the Fields<\/em> pane to the Legend<\/em> section of the Visualizations<\/em> pane. The legend determines how data will be clustered.<\/li>\n
            4. In the Visualizations<\/em> pane, click the OrderDate <\/em>column\u2019s down arrow, and then select Date Hierarchy<\/em>. Power BI Desktop automatically limits the hierarchy to the Year<\/em> level.<\/li>\n
            5. Drag the SubTotal <\/em>column from the Fields<\/em> pane to the Value<\/em> section of the Visualizations<\/em> pane. The value serves as the measure used for aggregating the data.<\/li>\n
            6. Drag the Country <\/em>column from the Fields<\/em> pane to the Visual level filters<\/em> subsection in the Filters<\/em> section of the Visualizations<\/em> pane. Expand the Country<\/em> section and select United States<\/em> to include only that country.<\/li>\n
            7. In the Filters<\/em> section of the Visualizations<\/em> pane, expand the SubTotal <\/em>section, select is greater than<\/em> from the first drop-down list, type 1000000<\/em> in the text box directly below the list, and click Apply filter<\/em>. By applying this filter, only sales totals greater than $1 million will be included in the visualization.<\/li>\n
            8. Position, resize, and format the visualization as necessary.<\/li>\n<\/ol>\n
              Your visualization should now look similar to the one shown in the following figure, except that it won\u2019t include the red analytics line. You\u2019ll be adding that shortly.<\/p>\n
              <\/p>\n
              Analytics is a feature in Power BI Desktop that lets you add dynamic reference lines to a visual to provide additional insights into the data or reflect important trends. The analytic in the above figure shows the average sales amount based on values in the SubTotal <\/em>column. To add this analytic, take the following steps:<\/p>\n
                \n
              1. Ensure that the Clustered column chart<\/em> visual is still selected on the design surface.<\/li>\n
              2. In the Visualizations<\/em> pane, go to the Analytics<\/em> tab, expand the Average Line <\/em>section, and click Add.<\/em><\/li>\n
              3. In the text box at the top of the Average Line <\/em>section, change the default name to Average<\/em>.<\/li>\n
              4. From the Measure<\/em> drop-down list, select SubTotal<\/em> if it\u2019s not already selected.<\/li>\n
              5. Configure the formatting settings as desired. You format the analytic directly on the Analytics<\/em> tab when adding the analytic. For example, you can configure the analytic\u2019s color, percentage of transparency, and line style.<\/li>\n<\/ol>\n
                The following figure shows how I configured the analytic on my system.<\/p>\n
                <\/p>\n
                You can add whatever analytics you think will be serve your purpose. Note, however, that not all visualizations support analytics.<\/p>\n
                Creating a Filled Map<\/h2>\n
                In this last example, you\u2019ll add a Filled map<\/em> visual and then a Table<\/em> visual that contains data corresponding to the map. The Filled map<\/em> visual uses patterns or shading to show how values differ across geographic regions. The visual relies on Microsoft\u2019s Bing service to provide default map longitude and latitude coordinates. As a result, you must be online when working with the Filled map<\/em> visual for the map to fully render on the design surface.<\/p>\n
                To add a Filled map<\/em> visual based on the Sales<\/em> dataset, take the following steps:<\/p>\n
                  \n
                1. Click the Plus<\/em> button to add a new page to the report, and then click the Filled map<\/em> button on the Visualizations<\/em> pane.<\/li>\n
                2. Drag the StateProvince <\/em>column from the Fields<\/em> pane to the Location<\/em> section of the Visualizations<\/em> pane. Power BI Desktop will automatically use these values to come up with their coordinates, leveraging the Bing service.<\/li>\n
                3. Drag the SubTotal <\/em>column from the Fields<\/em> pane to the Color saturation <\/em>section of the Visualizations<\/em> pane. The column serves as the measure used for aggregating totals and arriving at the state shading.<\/li>\n
                4. Drag the Country <\/em>column from the Fields<\/em> pane to the Visual level filters<\/em> subsection in the Filters<\/em> section of the Visualizations<\/em> pane. Expand the Country<\/em> section and select United States.<\/em>.<\/li>\n
                5. Position, resize, and format the visualization as necessary.<\/li>\n<\/ol>\n
                  That\u2019s all there is to adding the Filled map<\/em> visual. You can then add the Table<\/em> visual, using the following steps:<\/p>\n
                    \n
                  1. Ensure that the Filled map<\/em> visual is not selected on the design surface, and then click the Table<\/em> button on the Visualizations<\/em> pane.<\/li>\n
                  2. Drag the StateProvince,<\/em> FullName,<\/em> and SubTotal<\/em> columns from the Fields<\/em> pane to the Values <\/em>section of the Visualizations<\/em> pane.<\/li>\n
                  3. Drag the Country <\/em>column from the Fields<\/em> pane to the Visual level filters<\/em> subsection in the Filters<\/em> section of the Visualizations<\/em> pane. Expand the Country<\/em> section and select United States.<\/em><\/li>\n
                  4. Position, resize, and format the visualization as necessary.<\/li>\n<\/ol>\n
                    Your visualizations should now look similar to those shown in the following figure.<\/p>\n
                    <\/p>\n
                    Power BI Desktop automatically links visualizations located on the same report page. As a result, when you select an item in one visualization, the data is updated in the other visualizations. For example, in the following figure, Washington state is selected in the Filled map<\/em> visual. As a result, the Table<\/em> visual now includes only data about sales in Washington state.<\/p>\n
                    <\/p>\n
                    Linked visuals can be a handy way to allow users to gain quick insights into the same data from different perspectives, without having to create lots of extra visualizations. This also works for slicers as well, which are added at the page level, again providing a simple way to offer different perspectives into the data.<\/p>\n
                    Lots More Where That Came From<\/h2>\n
                    Power BI Desktop supports a number of visualizations in addition to what we\u2019ve covered here. For most of those visualizations, you can apply many of the same concepts that were demonstrated in this article. Power BI Desktop also supports the ability to use R scripts to create visualizations or to import R-based custom visualizations, as described in the Part 3<\/a> of this series.<\/p>\n
                    Regardless of the visualization type, the best way to learn how to build reports in Power BI Desktop is to experiment with the visualizations themselves, trying out different ones against the same data or different data. The more time you spend working directly with these features, the better you\u2019ll understand their capabilities and the quicker you\u2019ll be able to create visualizations that provide meaningful insights into the data. Power BI Desktop offers a solid foundation for building effective business intelligence reports, but it\u2019s up to you to figure out what it takes to get there.<\/p>\n","protected":false},"excerpt":{"rendered":"
                    Power BI contains rich visualizations that allow anyone to build insightful dashboards. In this article, Robert Sheldon shows you how to create Power BI charts, tables, maps, and slicers,   …<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[6779],"class_list":["post-80470","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=80470"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80470\/revisions"}],"predecessor-version":[{"id":80494,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80470\/revisions\/80494"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=80470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=80470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=80470"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=80470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}