Category: excel Power View

Excel Power View – Useful Resources ”; Previous Next The following resources contain additional information on Excel Power View. Please use them to get more in-depth knowledge on this. Useful Video Courses Excel – A Step by Step Complete Course 56 Lectures 6 hours TELCOMA Global More Detail Excel Makro ve VBA Eğitimi-1 37 Lectures 6.5 hours Volkan Yurtseven More Detail Excel Pivot Tables – Crash Course Most Popular 18 Lectures 47 mins EdSolver Learning More Detail Learn MS Excel, Data Analysis, and Financial Analysis 50 Lectures 3 hours Tayana Grubisic More Detail Excel Analytics – Data Analysis with Pivot-Tables and Charts 47 Lectures 2.5 hours Blink Data More Detail Learn microsoft excel course for data analysis Zero to hero 50 Lectures 2 hours ADITYA More Detail Print Page Previous Next Advertisements ”;

Discuss Excel Power View ”; Previous Next Power View enables interactive data exploration, visualization, and presentation that encourages intuitive ad-hoc reporting. Large data sets can be analyzed on the fly using versatile visualizations in Power View. The data visualizations are dynamic, thus facilitating ease of presentation of the data with a single Power View report. Print Page Previous Next Advertisements ”;

Key Performance Indicators ”; Previous Next A Key Performance Indicator (KPI) is a quantifiable measurement for gauging business objectives. Examples of KPIs are − Sales department of an organization use a KPI to measure monthly gross profit against projected gross profit. Accounting department measure monthly expenditures against revenue to evaluate costs. Human resources department measure quarterly employee turnover. Business professionals frequently use KPIs that are grouped together in a business scorecard to obtain a quick and accurate historical summary of business success or to identify trends. KPIs are a form of communication involving the following activities − Identifying the KPIs based on the organization’s objectives. Monitoring and reporting the KPIs. Altering the KPIs as the organization progresses and / or the organization’s goals change. The second and third objectives given above can be achieved with Power View. A KPI includes Base Value, Target Value/ Goal and Status. Base Value A Base Value is defined by a calculated field that resolves to a value. The calculated field represents the current value for the item in that row of the Table or Matrix. For example, aggregate of sales, profit for a given period, etc. Target Value A Target Value (or Goal) is defined by a calculated field that resolves to a value, or by an absolute value. The current value is evaluated against this value. This could be one of the following A fixed number that is the goal all the rows should achieve. E.g. Sales target for all the salespersons. A calculated field that might have a different goal for each row. For example, Budget (calculated field), department-wise in an organization. Status Status is the visual indicator of the value. In Power View, you can edit the KPI, choosing which indicators to use and what values to trigger each indicator. Identifying the KPIs The first and the most crucial step in KPI analysis is to identify the KPIs that effectively monitor the required trends in the organization. This requires complete understanding of the objectives and requires proper communication channels between the analysts and those who are responsible for fulfilling the objectives. There are a number of KPIs to choose from, but the success in monitoring relies on the right choice of those that are relevant to the objectives. The KPIs differ from organization to organization and from department to department. It is effective only when they lead to improvement in the performance. You can evaluate the relevance of a KPI using the SMART criteria, i.e. the KPI should be Specific, Measurable, Attainable, Relevant and Time-bound. In other words, the KPI chosen should meet the following criteria − The KPI reflects your Specific objective. The KPI enables you to Measure progress towards that goal. The goal for which the KPI is being defined is realistically Attainable. The goal that the KPI is targeting is Relevant to the organization. You can set a Time-frame for achieving the goal so that the KPI reveals how near the goal is as compared to the time that is left. The defined KPIs are to be evaluated from time to time to find their relevance as the time progresses. If required, different KPIs need to be defined and monitored. The KPIs might have to be edited as the time progresses. Only then, your KPI monitoring will be relating to the current organization needs. Defining KPIs in the Data Model Once you identify the KPIs, you can define them in the Data Model and add them to your Power View report to show the status of the set goals. You can do any of the following − Define the KPIs in the Data Model and use them as fields in Power View to visualize them. Define and / or edit the KPIs in Power View. You will learn how to define KPIs in the Data Model in this section. The other method you will learn in the next section. Suppose you want to monitor the sales targets of the salespersons in an organization who are selling a product. The objective is to identify the best performers who are meeting the Sales Target. You can proceed to define the KPI as follows − Base Value − Current Value of the Sales Amount for each salesperson. Target Value / Goal − Target Value is fixed for all the salespersons so as to enable comparison between the salespersons. Assume that the Sales Target is 3500. Status − The Status is to be displayed with a graphic to easily determine the status of the Base Value compared to the Target Value. Define the KPI in the Data Model as follows − Consider the two data tables – SalesPerson and Sales. Create a relationship between the two data tables using the field SalesPerson ID. To set the Base Value, you need a calculated field for Sales Amount. Click the Data View in the Power Pivot window. Click in the cell at the bottom of the column Sales Amount Type the following DAX formula in the formula bar. Total Sales:=SUM([Sales Amount]) Click in the Excel window. Click the POWERPIVOT tab on the Ribbon. Click KPIs in the Calculations group. Select New KPI from the dropdown list. The Key Performance Indicator (KPI) dialog box appears. Select Total Sales in the KPI base field (value) box. Under KPI Status, do the following − Under Define target value, select Absolute value and type 3500 in the box. Under Define status thresholds, adjust the vertical bars representing the percentages to 40 and 80. Under Select icon style, select the first option. Click OK. To check the KPI in the Data Model, do the following − Click in the Power Pivot window. Select the diagram view. The Total Sales appears as a KPI field in the Sales data table. Visualization of KPIs in Power View Create a Power View sheet to visualize the KPI that you have defined. You will observe the following in the Sales table in Power View Fields list − Total Sales

Excel Power View – Sharing ”; Previous Next Power View visualizations are interactive in nature with appealing look and feel. For any type of data and for large data sets you have suitable visualizations that will enable you to explore the data with presentable reports. So, you do not have an added step of preparing and /or previewing reports. Even formatting is interactive in nature and need not necessarily be the final step. Once you have explored and summarized your results, you would have to share with the concerned people. In this chapter, you will learn the different ways that you can share Power View reports. Sharing Power View in Excel You can share your Excel workbooks with Power View sheets on a SharePoint Server 2013 or SharePoint Online site. Your report readers can view and interact with the Power View sheets in the workbooks you have saved there. Sharing Power View in SharePoint Reports You can create and share Power View reports in SharePoint (RDLX files). The readers can view them, interact and /or edit if permissions are given on the server. Power View in Excel and Power View in SharePoint Server both provide an interactive data exploration, visualization and presentation experience for all skill levels. You can also export an interactive version of your Power View in SharePoint report to PowerPoint. Each view in Power View becomes a separate PowerPoint slide. You can interact with the visualizations and filters in each view, but you can’t create visualizations or filters. Printing Power View Reports You can print a Power View sheet. However, it will be a static image on paper. It will not be an interactive report, which is innate strength of Power View. You design a Power View report, on screen, with the look and feel you have in mind, which cannot be reflected on paper. Your visualizations are meant to be dynamic and cannot be captured by static images on paper to the fullest extent. Publishing to Power BI You can also publish Excel workbooks with Power View sheets to Power BI. Power BI saves the Power View sheets in your workbook as a Power BI report. Print Page Previous Next Advertisements ”;

Excel Power View – Map Visualization ”; Previous Next You can use Maps to display your data in the context of geography. Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. To make maps work, Power View has to send the data to Bing through a secured web connection for geocoding. Therefore, it asks you to enable content, adding locations and values places dots on the map. The larger the value, the bigger will be the dot. When you add a multi-value series, you will get Pie charts for data points on the map, with the size of the Pie Chart showing the size of the total. Your data has a geographic field Country that can be used for Map visualization. To create a Map visualization for the Medal Count with Country values, proceed as follows − Click the POWERVIEW tab on the Ribbon. Click Power View in the Insert group. A new Power View sheet will be created in the workbook. Setting Data Category for Geographic Field To set the data category for Geographic Field proceed as follows − Select the fields – Country and Medal Count. By default, Table will be displayed. You have a geographic field and a numeric field. You need to set the category of the field Country for Power View to create reports dynamically. Click the POWERPIVOT tab on the Ribbon. Click Manage in the Data Model group. The Power Pivot window appears. Click the Results tab. Select the Country field. Click the Advanced tab on the Ribbon. Click Data Category. Select Country/Region from the dropdown list. The Data Category of the field Country will be set as Country/Region. Click on the Power View sheet. A small globe icon appears beside the field Country in the Results table in the Power View fields list. This indicates that the field Country contains a geographic location. Switching to Map Visualization Convert the Table to Map visualization as follows − Click on the Table. Click the DESIGN tab. Click Map in the Switch Visualization group. The Table visualization will be converted to Map visualization. Power View creates a Map with a dot representing each geographic location, Country. The size of the dot is the value of the corresponding numeric field Medal Count. In the Power View Fields pane, the geographic field Country is in the LOCATIONS area and the numeric field Medal Count is in the ∑ SIZE area To display more information about a data point, you can do one of the following − Step 1 − Place the cursor on a dot on the Map. A box appears displaying the geographic location name and the corresponding numeric value. Step 2 − Click on a dot on the Map. That particular dot will be highlighted and all other dots will become inactive. Zooming and Panning in Map You can find the zoom in, zoom out, pan buttons on the top right corner of the Map when you hover the mouse on that area. Pie Charts in Map Visualization Suppose you want to add another field to the Map visualization. For example, you might want to display the medal types – Gold, Silver, and Bronze. You can do it as follows − Drag the field Medal to the COLOR area in the Power View Fields pane. The dots will be converted to Pie Charts. A Legend for Medal appears displaying the types of medals and respective colors. Each color in the Pie Charts represents the type of the medal as given in the Legend. You can also observe that the size of a Pie Chart corresponds to the Medal Count and the size of each slice in the Pie Chart corresponds to the count of that medal type. Exploring Data with Pie Charts in Map Visualization You can filter your data and highlight a significant data point as follows − In the Filters area, set the Medal Count to display only the values greater than or equal to 400. Apply the filter. The Map zooms and displays only the filtered values. Place the cursor on the Pie Chart representing Great Britain and the details of the Pie Chart will be displayed. You can see that the gold Medal Count for Great Britain is 545. You can find the silver Medal Count and bronze Medal Count for Great Britain by placing the cursor on those slices on the Pie Chart. Highlighting a Pie Slice in Map Visualization You might want to highlight the gold Medal Count of all countries Click on Gold in the Legend. The Pie slices representing Gold in all Pie Charts are highlighted. The other Pie slices in all the other Pie Charts are inactive. Place the cursor on any Pie Chart on the Map. The Pie slice representing gold will be highlighted. The details of the Pie slice will be displayed. Print Page Previous Next Advertisements ”;

Excel Power View – Formatting a Report ”; Previous Next Once your data visualization and data exploration is complete, you will be ready to produce reports for presentation. Power View provides a wide range of Themes, Chart Palettes, Fonts, Background Colors, etc. that can help you make your reports appealing. In this chapter, you will learn about the various formatting features of Power View. You know that it is possible to have a combination of Power View visualizations on a single Power View report. Some of the formatting options enable you to apply the same feature for the entire view and some options can be set separately for each of the visualizations. Further, some options will get replicated to all the Power View sheets in your workbook, while some would not. The formatting features in Power View enable you to do the following − Change the theme. Add background image. Choose background formatting. Change the text size. Change the font. Format numbers in Table, Matrix or Card. Changing Theme Consider the following Power View report − Change the Theme as follows − Click the POWERVIEW tab on the Ribbon. Click Themes in the Themes group. Select Composite from the dropdown gallery. The Theme of the Power View report changes to the selected one. A theme has a unique set of colors and fonts for creating a look and feel for the entire report. You can choose the theme that best suits your data, context, the background of the presentation, etc. Click in another Power View sheet in your workbook. You can observe that the new theme is applied to all the Power View sheets in the workbook. Setting Background Image You can add a background image in your Power View report, adjust its position, size and transparency. Consider the following Power View report that is filtered to show the results only for the year 2000. In 2000, Olympics were held in Sydney. You can add the respective emblem to your Power View report as follows − Click the POWERVIEW tab on the Ribbon. Click Set Image in the Background Image group. Select Set Image from the dropdown list. Browse for the image file and open it. The image appears as a background image in the Power View report. You can resize the background image in several ways − Stretch to occupy the entire Power View canvas. However, the aspect ratio might be lost and is not allowed for certain images. Tile to cover the entire Power View Canvas with multiple copies of the image. Center align the image. Fit to display with the right aspect ratio and cover the Power View canvas. This is also the by default, Fit option. The Center alignment option looks as shown below. You can specify the transparency of the background image. By default, it is 50%. The higher the percentage, the more transparent (less visible) the image. Click the POWERVIEW tab. Click Transparency in the Background Image group. Select 80% from the dropdown list. Change the Image Position to Tile. You can observe that the background image is set only for this Power View sheet and is not replicated in other Power View sheets in your workbook. You can remove the background image that you have set. Click the POWERVIEW tab on the Ribbon. Click Set Image in the Background Image group. Select Remove Image from the dropdown list. The background image will be removed. Changing Background Colors You can change the background colors in the Power View report. Click the POWERVIEW tab on the Ribbon. Click Background in the Themes group. You will find different backgrounds in the gallery, from solids to a variety of gradients. By default, it is white. Click Light2 Center Gradient. The background color changes to the selected one. The selected background color will be applied to all the Power View sheets in your workbook. Changing Font in a Power View Report You can change the Font in the Power View report. Click the Power View tab on the Ribbon. Click Font in the Themes group. Select Verdana from the dropdown list. The font is changed in all the visualizations. You can have only one font for all the visualizations in your Power View report. Note − The font has not changed in the other Power View sheets in your workbook. Changing Text Size in a Power View Report You can change the size of text to a percentage of the original text size in your Power View report. Click the Power View tab on the Ribbon. Click Text Size in the Themes group. By default, it is 100%. Select 125%. The font size of the text in the entire Power View report will be enlarged. Adjust the sizes of the visualizations and the title so that they will be conspicuous. You can see that the text in the visualizations have become more readable. Note − The text size would be the same in all the visualizations in the report and all the other Power View sheets in your workbook as well. Formatting Numbers in a Power View Report You can format numbers in Table, Matrix and Card visualizations. Create a Table with the fields – Country, Medal and Medal Count. Switch to Matrix visualization. Click on a value in the Medal Count column in the Matrix. Click the DESIGN tab on the Ribbon. Select Number from the dropdown list in the Number group. The entire column will be formatted to number. Click Decrease Indent in the Number group twice. The data will be displayed in Number format. You can format numbers in Table and Card visualizations also. Changing Number Aggregates In the Power View Fields list, some number fields will have a Sigma ∑ symbol next to them. They are aggregates, meaning that you can combine the values in that field to yield a numeric value such as sum, count, or average. You can aggregate a numeric or text (non-numeric) field. However, you cannot

Excel Power View – Matrix Visualization ”; Previous Next Matrix visualization is similar to a Table visualization in that it also contains rows and columns of data. However, a Matrix has additional features such as hierarchy, not repeating values, etc. As you have learnt in the previous chapters, you need to start with a Table and then convert it to Matrix. Choose the fields – Country, Sport, and Medal Count. A Table representing these fields appears in Power View. Switching to Matrix Visualization Convert the Table to Matrix as follows − Click on the Table. Click the DESIGN tab. Click Table in the Switch Visualization group. Select Matrix from the dropdown list. The Table is converted to Matrix. Advantages of Matrix Visualization A Matrix has the following advantages − It can display the data without repeating values. It can display totals and subtotals by columns and/or rows. If it contains a hierarchy, you can drill down/drill up. It can be collapsed and expanded by rows and/or columns. Combination of Table and Matrix Visualizations You can see the differences between the Table and Matrix visualizations by having them side by side on the Power View sheet, displaying the same data. Follow the steps given below − Create a Table with the fields – Country, Sport, Event, and Medal Count. In the Table, the values of country repeated for several sport values and the values of sport are repeated for several event values. Create another Table on the right side of the first Table as follows − Click on the Power View sheet in the space to the right of the Table. Select the fields – Country, Sport, Event, and Medal Count. Another Table representing these fields appears in Power View, to the right of the earlier Table. Click the Table on the right. Click the DESIGN tab on the Ribbon. Click Table in the Switch Visualization group. Select Matrix from the dropdown list. The Table on the right in Power View is converted to Matrix. As you can observe, the Matrix displays each country and sport only once, without repeating values as is the case in Table. Filtering Matrix in Power View You can explore the data to find the countries and the corresponding sports and events with medal count of more than 150. Click on the Table. In the Filters area, click the TABLE tab. Set the filtering criteria for Medal Count as – is greater than or equal to 150. Click Apply filter Click Matrix. In the Filters area, click the MATRIX tab. Set the filtering criteria for Medal Count as – is greater than or equal to 150. Click Apply filter. In Matrix, data is displayed without repeating the values, whereas in Table data is displayed with repeated values. Totals To understand the capability of Matrix in displaying Subtotals and Totals, do the following − Add the fields Country, Sport, Event and Medal Count to Matrix. As you can see, the fields – Country, Sport, and Event define the hierarchy and are nested in that order. Matrix also displays Subtotals at each of these Levels as shown below. The Subtotals and Total are given as follows − Medal Count is at the Event Level. Subtotal at the Sport Level – Sum of the Medal Count values of all Events in that Sport won by the Country that is one Level up. Subtotal at the Country Level – Sum of the Subtotals at Sport Level. At the bottom of the Matrix, the Total row is displayed that sums up all the Medal Count values. Look at a variation of the same Matrix − Add the fields Country, Sport, and Medal Count to Matrix. Filter the Matrix to display only values with Medal Count more than 250. The Medal Count values are displayed as follows − At Sport Level − Total Medal Count of all the Medal Counts at Event Levels in the Sport. At Country Level − Subtotal of all the Medal Count values at Sport Levels in the Country. Total Row − Total of all the Subtotals of all the Countries. If you do not want to display the Subtotals and Total rows in Matrix, do the following − Click on the Matrix. Click the DESIGN tab. Click Totals in the Options group. Select None from the dropdown list. Totals will not be displayed. To display the Subtotals and total again, do the following Click on the Matrix. Click the DESIGN tab. Click Totals in the Options group. Select Rows from the dropdown list. The Rows with Subtotals and Total will be displayed. As you can see, this is the default mode in Matrix. Print Page Previous Next Advertisements ”;

Excel Power View – Hierarchies ”; Previous Next If your Data Model has a hierarchy, you can use it in Power View. You can also create a new hierarchy from scratch in Power View. In both the cases, you can drill up and drill down the hierarchy in Power View. In this chapter, you will learn how to view the hierarchy and drill up and drill down the hierarchy in different Power View visualizations. Viewing a Hierarchy from Data Model If you have a hierarchy in Data Model, you can visualize the same in Power View. Suppose, you have the hierarchy Sport-Event defined in the Data Model as shown below. The hierarchy will be visible as a field in the Power View Fields list and you can add it as any other field to a Power View visualization. Create a Table with the fields – Country, Sport-Event and Medal Count. Switch visualization to Matrix. The levels in the hierarchy are nested as per the order of fields in the hierarchy. Creating a Hierarchy in Power View You can also create a new hierarchy from scratch in Power View. Create a Table with the fields – Country, Sport, Event, Year, and Medal Count, in that order. Switch visualization to Matrix. The hierarchy is set by the order of the fields in the ROWS area. You can place the fields in any order in a hierarchy in Power View, provided it is meaningful. You can change the order by simply dragging the fields in the ROWS area. The difference between defining the hierarchy in Data Model and defining the hierarchy in Power View is the following − If you define a hierarchy in Data Model, it is added to Power View Fields list as a field and you can include it in any visualization in Power View by just adding that field. On the other hand, if you define a hierarchy in Power View, it is restricted to the visualization in which you have placed the fields in the hierarchy order. It needs to be recreated in every visualization that is in the Power View. Drilling Up and Drilling Down the Hierarchy in Matrix Once you have a hierarchy in Power View (either from Data Model or from Power View), you can drill up and drill down in Matrix, Bar Chart, Column Chart and Pie Chart visualizations. In this section, you will understand how you can drill up and drill down the hierarchy in Matrix visualization. In the subsequent sections, you will understand how to do the same in the other mentioned visualizations. In Matrix, you can show just one level at a time. You can drill down for details and drill up for summary. Click on the Matrix. Click the DESIGN tab on the Ribbon. Click Show Levels in the Options group. Select Rows – Enable Drill Down One Level at a Time from the dropdown list. The Matrix collapses to display only Level 1 data. You can also find an arrow on right side of the Level 1 data value indicating drill down. Click on the drill down arrow to drill down. Alternatively, you can double click on the data value to drill down. That particular data value drills down by one Level. For the data value, you have one arrow on the left indicating drill up and one arrow on the right indicating drill down. You can double click on one data value in a Level to expand to show the data values under that in the next Level in the hierarchy. You can click on the drill up arrow to collapse to the data value. Hierarchy in Bar Chart In this section, you will understand how you can drill up and drill the hierarchy in a Stacked Bar Chart visualization. Create a Table with the fields – Country, Sport-Event and Medal Count. Sport-Event is a hierarchy with fields Sport and Event that is defined in the Data Model. Switch visualization to Stacked Bar Chart. Ensure Country, Sport, Event are in the AXIS area. Add the field Medal to LEGEND area. A Stacked Bar Chart will be displayed. The data displayed is Medal Count by Country and Medal. Double-click a Bar, say CAN. The Stacked Bar Chart will be drilled down by one level. The data displayed is Medal Count by Sport and Medal (This is for the Country – CAN). A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in. Now, you can either drill up to Country Level or drill down to Event Level. Double click on the Bar – Figure Skating. The Stacked Bar Chart will be drilled down by one level. The data displayed is Medal Count by Event and Medal (This is for the Country – CAN and Sport – Figure Skating). A small up arrow, indicating drill up appears in the top right corner of the Chart, adjacent to Filter and Pop-in. Now, you can drill up to Sport Level (You can drill up one level at a time). Click he drill up arrow. The data displayed will be Medal Count by Sport and Medal (for Country – CAN). Click the drill up arrow. The Stacked Bar Chart will be drilled up to Country Level. Hierarchy in Column Chart In this section, you will understand how you can drill up and drill the hierarchy in a Stacked Column Chart visualization. Create a Table with the fields – Country, Sport-Event and Medal Count. Sport-Event is a hierarchy with fields Sport and Event that is defined in the Data Model. Switch visualization to Stacked Column Chart. Ensure Country, Sport, Event are in the AXIS area. Add the field Medal to LEGEND area. A Stacked Column Chart will be displayed. The data displayed is Medal Count by Country and Medal. Double-click on a Column, say CHN. A Stacked Column Chart will be drilled down by one level. The data displayed is

Excel Power View – Pie Chart Visualization ”; Previous Next Pie charts in Power View can be simple or sophisticated. You will learn about these two types of Power View visualizations and data exploration and visualization with Pie Charts in this chapter. As you know, you need to start with a Table and then convert it to Pie Chart visualization. Select the fields – Country and Medal Count. By default, Table will be displayed. Add two more Tables to Power View with the same fields. Adjust the size of the visualizations to appear as below. Click on the Table to the top right side of the Power View. Click the DESIGN tab on the Ribbon. Click Column Chart in the Switch Visualization group. Click Stacked Column. The Table will be converted to Column Chart. Adjust the size of Column Chart to display more number of Country values. Switching to Pie Chart Visualization Convert the Table at the lower portion of Power View to Pie Chart as follows − Click on the Table. Click the DESIGN tab. Click Other Chart in the Switch Visualization group. Select Pie from the dropdown list. The Table visualization will be converted to Pie Chart visualization. A warning message appears at the top of the Chart – Too many ‘Çountry’ values. Not displaying all data. Filter the data or choose another field. You can see that there are too many slices in the Pie chart as there are many Country values. Note that Pie Charts work well only when the number of categories is 8 or less. You can reduce the number of categories by filtering the values as follows − Set the filtering criteria as Medal Count is greater than or equal to 1300 in all the visualizations in Power View. You have a Simple Pie Chart visualization, wherein the Medal Count values are shown by the Pie sizes and Country values by colors, as shown in the Legend. Exploring Data with Simple Pie Chart Visualization You can explore data interactively with Simple Pie Chart as follows − Click on a Pie slice. That slice will be highlighted and others will be inactive. The corresponding Column in the Column Chart also will be highlighted. In the Table, only the values corresponding to the highlighted Pie slice will be displayed. Place the cursor on the highlighted Pie slice. The data values corresponding to that Pie slice will be displayed. Sophisticated Pie Chart Visualizations You can make your Pie Chart visualization sophisticated to add more powerful data exploration features. You have two types of Sophisticated Pie Chart visualizations − Pie Chart that drills down when you double-click on a Pie slice. Pie Chart that shows sub-slices within the larger Pie slices. Exploring Data with Sophisticated Pie Chart Visualizations Exploring Data with Pie Chart that drills down Click on the Pie Chart. Drag the field Gender to COLOR area, to below the field Country in the Power View Fields pane. This means you have two categories. In the Table, add Gender to FIELDS. In the Column Chart, add Gender to LEGEND area. In the Pie Chart, there is a single slice with one color for each category – Country. Double-click on the USA slice. The Pie Chart in your Power View will be changed to show values by Gender, which is the second category, for the selected first category (USA). The colors of the Pie Chart now show the percentages of the second field, i.e., Gender, corresponding to Country – USA. In other words, the Pie Chart was drilled down. A small arrow – Drill up appears on the top right corner of the Pie Chart. Click on a Pie slice. That Pie slice will be highlighted and the other one will become inactive. The Table is filtered showing only the values corresponding to the highlighted Pie slice. In the Column Chart, the portion of the Column corresponding to the highlighted Pie slice is highlighted and the rest becomes inactive. Click the drill up arrow. The Pie Chart returns to its previous state. Click on the Pie slice – USA The Pie slice is highlighted. The Table is filtered to show only those values. In the Column Chart, the Column corresponding to the Pie slice is highlighted. Exploring Data with Pie Chart that shows sub-slices To explore data with Pie chart that shows sub-slices, proceed as follows − Click on the Pie Chart. Drag the field Gender from COLOR area to SLICES area in the Power View Fields pane. In the Pie Chart, there are two Pie slices of same color for the Country value – USA. Click on one of these Pie slices. You will observe the following changes in Power View − In the Pie Chart, the selected Pie slice is highlighted and other slices are grayed out. In the Column Chart, the Column for the Country value USA highlights the Medal Count for the selected Pie slice. The Table shows only the values corresponding to the selected Pie slice. Click on the other Pie slice of the Country value USA. You will observe the changes as given above for this selected Pie slice. Print Page Previous Next Advertisements ”;

Column Chart Visualization ”; Previous Next Column Charts are used for showing data changes over a period of time or for illustrating comparison among items. In Column Charts, categories are along the horizontal axis and values along the vertical axis. You have learnt in the previous chapters, you need to start with a Table and then convert it to Column Chart visualization. Select the fields – Country and Medal Count. By default, Table will be displayed. Types of Column Chart Visualization In Power View, there are three types of Column Chart visualization − Stacked Column. 100% Stacked Column. Clustered Column. Switching to Column Chart Visualization Convert the Table to Column Chart as follows − Click on the Table. Click the DESIGN tab. Click Column Chart in the Switch Visualization group. Select Stacked Column from the dropdown list. The Table will be converted to Column Chart. The X-axis values are sorted by the category values in ascending order. Exploring Data with Column Chart Visualization You can explore data with Column Chart in several ways. You will understand the various methods that you can use for data visualization and exploration with Column Chart in this section and the subsequent sections. In the Column Chart, the categories are distributed evenly along the x-axis and not all the categories (countries in this case) are visible in the display. To view the categories that are not in the display, do the following − Click and hold on the Category (x-axis) axis. Drag to left or right. The categories to the left or right will be displayed and the Column Chart will be displayed accordingly. The Status Bar shows you the position of the current display as compared to the complete category range. You can drag the Status Bar also to the left or right to display the categories that are on the left or right side of the current display. You might want to know the data values of a Column on the Column Chart. Place the cursor on the Column on the Column Chart. The values corresponding to that Column will be displayed at that Column. Sorting in Column Chart You can sort the X-axis values in Column Chart by the Medal Count as follows − On the top left corner, you will see – sort by Country asc. This means the sorting is by Country and in ascending order. Click on Country. It will change to Medal Count. Click on asc. It will change to desc. The Column Chart will be sorted by Medal Count in descending order. Combination of Column Chart and Table Visualizations You can view the interactive features of Column Chart visualization by placing a Table on the same Power View. Create a Table with the fields – Country and Medal Count. Adjust the sizes and positions of Column Chart and Table to appear as below. Click on the Filters area. As you have not yet selected any fields for filtering, the Filters area will be empty. Drag the field Country from Power View Fields list to the Filters area. Check the boxes for the countries that you want display the results. Both Table and Column Chart are filtered to show only the filtered data. Click on the Columns for FRA, GER and ITA with the Ctrl key pressed. These Columns will be highlighted and others will become inactive. The Table is also filtered to show the values only for the highlighted Columns. Adding a Legend You will understand some powerful features of a Column Chart by adding a Legend. Arrange the Column Chart and Table to be side by side. Click on the Column Chart. Drag the field Medal to LEGEND area. Click on the Table. Drag the field Medal to FIELDS area. The illustration give above is the full-fledged Stacked Column Chart visualization, showing the Medal Count by Medal type for each Country. Click on the top portion of the Column for GBR. You will observe the following − Only the orange color portion of the Column for GBR on which you clicked will be highlighted. The other two portions of the Column for GBR will become inactive. All other Columns will become inactive. The Table will be filtered to show only the values of highlighted region. Note − You cannot make multiple selections in this case. 100% Stacked Column Chart You can convert the Stacked Column Chart to 100% Stacked Column Chart as follows − Click on the Stacked Column Chart. Click the DESIGN tab on the Ribbon. Click Column Chart. Select 100% Stacked Column from the dropdown list. The Stacked Column Chart will be converted to 100% Stacked Column Chart. From the Chart, you can visualize the Gold, Silver and Bronze ratios of the total Medal Count for each Country. You can also visualize the ratio of the Medals won by Men and Women for each Country. Remove the field Medal and add the field Gender to both Column Chart and Table. For CHN, the Medal Count of Women is more than that of Men as against other countries. Scroll down the Table to view the values for CHN. In country CHN, the Medal Count of Men is 268 and that of Women is 549. Click on the X-axis and drag to view the other countries. You can find other countries, where women outperformed men. Clustered Column Chart You can convert the 100% Stacked Column Chart to Clustered Column Chart as follows − Click on the 100% Stacked Column Chart. Click the DESIGN tab on the Ribbon. Click Column Chart. Select Clustered Column from the dropdown list. The 100% Stacked Column chart will be converted to Clustered Column chart. Click on the orange Column for CHN. You will observe the following − Only the selected Column will be highlighted. The other Columns will be deactivated. The Table also shows only the corresponding values. Combining Bar Chart Visualization To understand the interactive features of Power View Chart visualizations more in depth, place the