SharePoint Governance and The Balance

For many, SharePoint Governance is a document, a contract, between the people who support SharePoint and the rest of the organization that sets forth:

  •  policies,
  •  rules,
  • roles,
  • and responsibilities

of the system. Failure to manage these four areas could jeopardize the success of SharePoint in the organization.

 These four areas, like the four legs to a chair, provide a stable platform on which to build an organization’s SharePoint environment. SharePoint Governance can group these four areas into the following four groups within the organization.

  • Operational Management: This group defines the roles and responsibilities of those who are ultimately responsible for the SharePoint portals within the organization. This group can consist of a governance committee or simply a few of the organization’s top executives. They identify the overall features of SharePoint that will be used within the organization. Effectively, this group defines the policies related to SharePoint.
  • Technical Operations: This group defines the technical structures of how SharePoint will be deployed, any software and hardware requirements, specific features to activate, uptime availability, backups, authentication, and which classes of users can access different elements of the portal effectively defining internal and external sites. These activities largely define the rules around the SharePoint implementation.
  • Site and Security Administration: This group is responsible for the creation and destruction of sites as needed along with defining site ownership and the corresponding responsibilities of different user groups within each site or class of sites. They define best practices on defining permissions and provide support on how best to organize site collections. Security within SharePoint is established by the individual’s role within the site.
  • Content Administration: This final group defines the nitty-gritty details of how to load and display content within the site. It is responsible for creating guidelines for the use of content types, workflows, metadata, and various web parts to achieve content goals. They may also help determine life-cycles for content retention policies and policies used to enforce the archiving and deleting of older content. This group identifies and assists users with their responsibilities for building and maintaining sites.

However, failure of SharePoint to succeed because one or more of the legs of that governance chair are not stable should not be indicative of an inherent problem with SharePoint. In fact, failure to create and then follow the governance policies, rules, or recommendations is more of an indication of the failure of the organization. If an organization cannot create a governance document that manages SharePoint usage, that is indicative of a greater potential problem, one in which top management may not support the use of the tool or understand its needs or benefits in the first place. This lack of support could be an early warning sign that the project may not be valued within the organization.

Even with governance for SharePoint or for any other product in place, it remains only a paper (or electronic) document unless management establishes an infrastructure to enforce it. Once policies, rules, roles, or responsibilities start to be bent or ignored in small ways, it is a slippery slope to the point where everyone ignores the governance document and chaos begins to take over. It may not be long before top management begins looking for a new solution, one that will magically cure all the current perceived problems. It may not occur to them that simply enforcing the original governance would alleviate most if not all of the current problems. On the other hand, enforcing strict standards in the name of governance is like putting blinders on a horse that could prevent the organization for discovering that there are better tools and better ways of doing things.

Furthermore, do not interpret governance to limit when or if an organization can switch tools or processes. Switching tools should always be possible, especially if another product with significantly better features or improved functionality becomes available. Governance does not address the issues of when a tool or process becomes obsolete. It merely addresses how to use that tool while it is in use.

At the same time that governance should define specific actions or activities, it should be a living document that can change over time to satisfy new demands. If those demands can be met by making small changes to the product or the way it is used, the overall costs of meeting the organizations needs will be minimized. Thought of in another way, governance is nothing more than a roadmap in which the organization can achieve the maximum benefits from a process or tool while minimizing the costs. It keeps everyone moving in the same direction rather than letting everyone to go off in different directions doing their own thing. Governance that is too strict can strangle an organizations ability to adapt and create new solutions to problems. Governance that is too loose will prevent directed and organized progress toward a goal.

A balanced governance approach can be in everyone’s best interest but can be difficult to obtain.

A Table of Contents for Your Site

Continuing on the theme from the last two weeks, I am going to take a look at another web part that is infrequently used, at least on the web sites that I work with. We use the Site Navigation feature out of the box for navigating between pages on a site and even to add custom links to pages and sites outside of our site. We also find the Site Navigation feature lets us build pseudo-hierarchies by adding header entries and links that serve as a fly-out to the header. But there is another way to provide navigation around a site. That is with the Table of Contents Web Part.

The Table of Contents web part lets me display all of the pages and sites that branch off the current site. The key advantage to this web part is that I can display up to three site levels (the current level and two nested sub-site levels) on a single page. It also gives me some built-in formatting capabilities to change the way the Table of Contents is rendered on the page ranging from vertical orientation to a horizontal orientation and several hybrid types in between. So let’s take a look at this web part.

Like other SharePoint web parts, I have to add the Table of Contents web part to a page on my site. This may mean that I have to first create the page where I want to place the Table of Contents. Then I can edit the page and going to the Insert ribbon and select the Web Part button from the Web Parts group. In SharePoint 2010, the Table of Contents web part can be found in the Default category as shown in the figure below. However, in SharePoint 2013, it was moved to the Content Rollup category which actually makes some sense.

After select the Table of Contents web part, click the Add button on the bottom right of the dialog. After adding the web part to the page, SharePoint provides a default view of the site. This view displays pages and sub-sites off the current site as shown below or it may only display sub-sites and their pages. In either case, I can easily modify the properties to control where the Table of Contents begins.

As with all web parts, I can edit the properties by hovering over the header to display the down-pointing caret on the right side of the header. This is the web part menu. I click on it to open the menu and select: Edit Web Part. The properties panel for the web part appears to the right of the page. You may need to scroll to the right and up the page to find it depending on the size of your page. The most interesting properties and the ones I will focus on appear in the top section of the panel. There are three sections: Content, Presentation, and Organization as shown in the following figure. Each has a box with a plus sign in it in. To open each section to view and modify its properties, click on the plus sign in the box to the left of the section name.

The following figure shows the properties found in the Content section. The first property identifies the site where the Table of Contents will begin. Note that you can either enter the URL or you can select it by clicking on the Browse button.

The second property identifies how many sub-site levels you want the Table of Contents to include. The maximum is three levels. There is also a check box where you can identify whether the site where you are beginning the Table of Contents should be included or whether to only include sub-sites under the current site and their pages.

Finally, this section has three check boxes where you can select whether you want to show pages (or just the sub-sites in the Table of Contents. You can also choose whether you want to display hidden pages or hidden sites. I suppose if you need to see everything in a site, you might check these two boxes, but most times I would guess that they were hidden for a reason.

The second section lets me modify some of the presentation features of the web part beginning with a header for the web part. Note that this is not the same as the web part Title that appears in the chrome of the web part. In fact, you may turn off the chrome and use this property to place a header at the top of the table of contents.

You can also modify the style of the header. The figure below shows the pre-defined styles available for the header.

You can also define the number of columns to divide the content area into. You can also modify the styles for the other levels. This will affect the sub-site entries in the Table of Contents.

The third section deals with organization of the items in the Table of Contents. By default, SharePoint orders the items in the Table of Contents to match the navigation. However, for large sites with many pages or many sub-sites, this may make it harder to find what you are looking for. Therefore, SharePoint provides a way to provide a custom ordering of sites and the pages within the sites.

If you select the custom sort option, you can sort the sites by Title, Creation Date, or Last Modified Date. You can also choose the direction of the sort as either ascending or descending. For example a descending sort on Creating Date will list the most recently created sites or pages near the top of the list making it easier to find what is new on the site.

After you have set all the properties, you can apply your changes and click OK to view your Table of Contents page.

That’s all for this week. C’ya next time.

Pulling It All Together with the Site Aggregator Web Part

We saw last time how I can easily see all the documents in the current site that I last modified or created with the Relevant Documents web part. However, what do I do if I want to see all of my documents in any one of several different sites? Do I have to navigate to each of these sites and open a page with a Relevant Documents web part?

Fortunately, there is an easier way! The Site Aggregator web part allows me to view my documents stored in any number of sites from a single place, sort of.

After reading my last blog article, I’m going to assume you know how to add a web part onto a page in your site. (If you skipped that blog, you can always go back to it at: As with the Relevant Documents web part, the Site Aggregator can be found in the Content Rollup category for both SharePoint 2010 and SharePoint 2013. After adding the web part to the page, it looks something like the following:

At first, I may be puzzled by the text telling me to click on the “Add New Tab” icon. The first thing I should know is that each site that I want to pull documents from will be displayed separately and that I must choose the site I want to view by clicking on a tab/link across the top of the web part. To add a new tab, I need to click on the icon that appears in the top right of the Site Aggregator that looks like a drive icon with a yellow asterisk in its upper right corner.

This button displays a dialog that lets me enter the name of the site that I want to view. For example, the following figure shows a reference to a demonstration school site. Note that the URL does not point to a specific page. Rather it is the URL of the site only. Also notice that the URL must end with a slash ‘/’. The second property in this dialog is the name that will appear in the tab/link across the top of the Site Aggregator.

When I click the Create button, the Site Aggregator shows the contents of all libraries in the selected site and lets me click on the document name to open the document directly or by clicking on the location, to go to the document’s library.

So far, that works pretty much like the relevant documents web part. The feature that makes this web part different is that I can click on the Add New Tab to add another URL to a different site. In fact, I can add several new tabs as shown in the image below which includes separate tabs to view the documents found in each of the individual grade sub-sites for this virtual school.

Notice how the tabs/links can actually require more horizontal space than the size of the page. When this occurs, double angle brackets appear at the beginning and end of the row to allow me to horizontally scroll through the tabs. I can also use the down pointing arrow to the right of the Add New Tab button to open a dropdown menu of the available tabs.

If I open the web part properties as described for the Relevant Documents web part, I will see the properties that I can modify for this web part. As before, I may want to change the title for the web part that appears at the top of the web part.

Two additional unique properties to this web part are in the View and URL groups. The View group has a single property that lets me control the number of characters that appear in the tab/link before ellipses replace the balance of the characters. According to the documentation, this feature can be used to control the number of characters used in the label. I believe that in SharePoint 2010, I must allocate 2 characters of this number to the ellipses to determine the actual number of characters displayed. For example, a value of 10 allows for 8 characters plus the ellipses. In SharePoint 2013, this property seems to be ignored in my test site. But that may just be my site. What do you get?

The URL group prompts me for a character string that it will add to the URL provided when I define a new tab to specify exactly what is returned by the web part. The default string: _layouts/MyInfo.aspx uses a predefined view that displays content from the site library that shows documents that I modified or created.

However, it also appears possible to replace this string with others. For example I could enter the string: _layouts/SiteManager.aspx.

This string opens the Site Content and Structure view which displays all the documents in all the libraries for the site.

Note that I can navigate to other sites as well as the current site by using the leftmost panel and then by selecting different views, quickly determine which documents I have checked out, have modified, are pending approval, or are still in draft mode.

In future months, I may examine some of the other lesser used web parts and explore their use.


Lesser Used Web Parts of SharePoint

This week I’m beginning my summer break from BI and returning to SharePoint to look at some of what I like to call the lesser used web parts. Some of these web parts I will discuss over the next several weeks did not exist in the original SharePoint 2007 (Original for me because that is when I started using SharePoint). Some of the web parts might not have even existed in SharePoint 2010, but only appeared in SharePoint 2013. So depending on which version of SharePoint your site is current on, you may or may not see some of the web parts I will describe. However, I will try to tell you whether the web part existing at least in SharePoint 2010 and/or SharePoint 2013. Some might even appear within different categories of web parts because Microsoft chose to regroup some web parts between 2010 and 2013. I will try to let you know that too. With that in mind, let’s begin with a web part that did exist in both SharePoint 2010 and SharePoint 2013:

Relevant Documents Web Part

Often the number of documents in a site becomes overwhelmingly large and finding the documents I worked on can be quite a challenge. This is especially true of collaboration sites. The Relevant Documents web part, which exists in both SharePoint 2010 and SharePoint 2013, helps me find what I want. Furthermore, my site does not need a custom view or custom page to display the documents relevant to each person who has edit rights to the site. This web part automatically detects the currently logged in user and filters the documents returned by that user. I don’t even need to know in which library to search in because this web part searches across all libraries in the current site (but not subsites). That means that it returns not only documents from the document libraries in the site regardless of the library names, but also items from image libraries and page libraries. Let’s see an example.

The Relevant Documents web part, like all web parts, must be hosted within a page. Therefore, I must first either create the page I want to use or navigate to an existing page.

Next I edit the page. Depending on the version of SharePoint, the Edit this page option may either appear in the Site Actions dropdown menu (2010), the Actions icon (2013), or the Edit button (2013).

I then find a place on the page where I want to add the web part and from the Insert tab, click on Web Part in the Parts group as shown in the following image.

SharePoint then displays three boxes across the top of the page beginning with Categories on the left. Select Content Rollup from the Categories list.

I now see the Relevant Documents web part in the Parts box. Select this web part by clicking on it.

Additional information about the selected web part then appears in the About the part box. To add the web part to my page, I simply click the Add button in the bottom right side of this area.

The following figure shows how this dialog looks in SharePoint 2013. However, the changes in SharePoint 2010 are minimal.

After I add the web part to the page, it automatically displays any documents in the current site that I last modified by default. The theory of this default is that documents I recently modified would be the most likely files I would want to return to edit further.

This web part does have some properties that I may want to tweak. To open the web part properties, hover over the web part title until the dropdown arrow appears on the right side of the header.

Select: Edit Web Part from the dropdown menu. I then need to scroll to the right and possibly up to see the properties panel. This dialog consists of several property groups. The first group: Appearance, is open by default. Here I can change the Title property to change the web part’s displayed title.

The other properties I may want to change can be found the Data group as shown below:

Note that there are separate options to let me see all documents that I created, even after someone else modifies it and documents which I may have checked out that others created and modified. The checkbox to include a link to the folder or list allows me to open the library rather than just opening the document. Finally, I can adjust the number of items shown in the list. However, my recommendation is that for most users, a number from 1 to 100 makes the most sense.

In conclusion, I could create a page on my site with the name My Relevant Documents. Then by using this web part, every contributor to my site can go to that one page to see only the documents that they have added or have been working on.

That’s all for this week. Hope you are having a good summer and next week I will continue with a related web part: Site Aggregator.


Map It For Me, Please

Last week I introduced Power View by creating a simple table and then a chart from that table. This week, I’ll do a quick overview of another visualization within Power View, the ability to display your data on a map.

To begin, I open Excel and build a data model with the data I want to visualize in a map. I need to specify location information using some of the fields in the data model. In theory, I can use anything that identifies where the measure I’m displaying takes place. Ideally, I would like to have latitude and longitude for each fact instance in the fact table, but that is not always possible or even necessary. For example, let’s begin by looking at the relative sales by city from the Contoso dataset.

After opening my Excel spreadsheet and building an appropriate data model, I return to the Excel window and from the Insert tab click the Power View button in the reports section.

This opens a new worksheet as shown below with a blank design area on the left and my field list from my data model on the right.

I then drag the fields I want to use in my data visualization to the Fields box at the bottom of the right panel. For this example, I will drag the CityName field from the Geography dimension and the SalesAmount field from the FactSales table. This gives me the two column table shown below with sorted by the city by default.

To change the visualization, I need to open the Design tab which appears when I click anywhere within the table in my design area. If I had multiple tables, I would have to be sure to click in the table for which I want to change the visualization first. Then from the Switch Visualization group, I select Map.

The default visualization, shown below, displays a bubble for each city that I have data. Each bubble’s size represents the relative sales amount derived from that city.

Because I did not specify a field to use as a group level for color, all of the bubbles initially appear the same color. However, I can easily specify a different color for each country by copying the field RegionCountryName to the Color parameter. This assigns a unique color to the bubbles within a country that is different compared with other countries. At first glance, everything may appear to be okay, but then I noticed a bubble in the southeast portion of the United States that had a different color. Hovering over that bubble, I see information about the bubble including the city name, the country, and the sales amount. In this case, the city is Saint Petersburg. However the country is Russia, not the United States (Saint Petersburg, Florida is perhaps what the map was thinking.) This occurred because the location criteria was only based only on city, not city within the country. In fact, if I zoom into the map further, I find other bubbles that placed the city in the wrong country.

One way to fix this issue is to use a field that has both the city name and country in it. However, you cannot create a new calculated column from within Power View. This type of change must occur in the data model. Therefore, I could return to the data model and open the DimGeography table to create a new concatenated field. This field combines the city and country names into a single new field: City_Country using the following formula:

= [CityName] & “, ” & [RegionCountryName]

The resulting new column appears in the following figure.

If I replace the [CityName] field with the [City_Country] field in the locations box as shown below, it appears at first glance that the problems with incorrectly positioned cities have been solved.

But again if I expand the map, I can find a few cities such as the one shown in the figure below that are not correctly positioned.

Honestly, I have not been able to figure out why a few cities are still displayed incorrectly. However, I have another way I can ‘fix’ the problem. First I turn on the Filters Area which has been turned off to maximize the size of the map.

I then drag the RegionCountryName field from the DimGeograph table over to the Filter panel. This shows me a list of unique values for this field. I can then use the check box to select one or more countries to display on the map at one time. For example, let’s just display the United States.

When I add the filter, the bubble for Cheshire, United Kingdom disappears. As you can see in the following figure. Changing the map background to Road Map Background, I get a more colorful map that might be more suited for a report that appears in color.

However, this is not the only way to filter data. I can also create a slicer in the design area by dragging the field by which I want to select data and dropping it in an empty part of the design area. The figure below shows me dragging the field RegionCountryName to the design area to the right of the map legend. This initially creates a single column table with the values from this field.

Next, without leaving the field, I can go to the Design tab to the right of the Power View tab and select the Slicer button from the Slicer group. This action converts the table into a slicer object that controls all the other objects in the current page.

Now I can filter the map to any country I want to focus on. Typically, selecting a country also zooms into the map to display that country as shown in the following image in which I selected Japan.

Any time that I want to return to the map displaying all the countries again I can click on the small blue ‘eraser’ button in the upper right corner of the slicer table. Note that this button only appears while the mouse is hovering within the slicer.

That’s it for this week. Next time I will look at some other features of Power View. C’ya.

A New View in Excel

I have used many of my blogs over the last couple of years to talk about PowerPivot in Excel 2010 and Excel 2013. By now you should have a pretty good idea how to build data models and analyze your data. You many even have used the Pivot Chart capability to display your data results visually. But did you know that there was another way to visualize your data within Excel 2013?

Just like PowerPivot that now comes preinstalled with Microsoft Office Excel Professional Plus 2013, so does Power View. To use this tool, you must also have installed Internet Explorer and Silverlight on your computer. If you have those prerequisites, you can enable Power View by opening the options dialog in Excel and selecting Add-Ins. At the bottom of the right panel is an option to manage different classes of Add-ins. Using the drop-down menu select: Com Add-ins and click the Go button. From the list of available add-ins, locate Power View and make sure the checkbox to the left is selected. Then click OK.

Next open the Insert ribbon in Excel 2013 and click the Power View button that appears in the Reports group. If this is your first time using Power View, you will need to enable it by clicking the Enable button that appears. This opens a separate Power View worksheet in the current workbook which will look something like the following:

If you do not have Silverlight installed, you will be prompted across the top of the Power View worksheet to do this. Click “Install” to install Silverlight. When the process completes, click the “Reload” button in the message bar to return to Power View.

In addition to the Power View worksheet, you should see a Power View tab with several options as shown below:

Because I started from a blank spreadsheet, I have no data I can use in my Power View report yet. In fact, the Power View Fields panel tells me that I need to create and select a range of cells with data and then click Power View from the Insert tab to proceed. For today’s quick example, I will create a simple table as shown below:

Once I’ve added this table to my Power View report worksheet, the Fields panel now shows me my active tables and the fields within those tables. Notice that numeric fields are automatically treated as aggregated sums. I can select the fields that I want to appear in my table by either clicking on the checkbox before the field name in the top half of the Power View Fields panel or I can drag the fields that I want down to the FIELDS box in the lower half of the panel. The advantage of dragging fields to the FIELDS box is that I can arrange the order of the fields here by dropping new fields in the position I want and I can even drag and drop fields later to rearrange the order of the fields in the table. The following figure shows the result of including all of the fields from the original table in my Power View table.

Of course if I don’t want to display all the fields, I can remove them by simply unchecking the check box in the upper portion of the Fields panel or I can use the dropdown menu to the right of any field in the FIELDS box to remove the field or change the aggregate function used for that field. For example, suppose I only was interested in the total student populations of each of the schools. I can simply remove the Student Grade column to achieve that result.

Next, suppose I wanted to have a second table or a chart on the same page. To do that, I begin by clicking in any blank area (not defined by the rectangle created by the first table). I then select the fields I want to include in my table or chart. In the example below, I use only the school name and student population just as was done in the first table.

I can then go to the Design ribbon associated with the Power View worksheet whose tab is displayed to the immediate right of the Power View tab. The first group contains options to switch the visualization of the data. The fourth icon, Other Chart, displays a dropdown that includes options to display the data as a line, scatter, and pie chart as shown below. There are some other interesting options in this ribbon like Map and Tiles that I will cover in future installments of this blog, but for now, let’s display a Pie chart of this data.

When I select PIE, Power View attempts to determine which fields to use for each part of the pie chart. In this case with only two fields, the answer is easy since only one of the fields is numeric. The numeric field is chosen as the size field that determines the size of the pie slice while the text field becomes the identifier of the slice which Power View calls Color. Note that there is no way currently to change the individual colors used in the pie chart. However, you can use the Themes dropdown in the Themes group of the Power View ribbon to change the color set used. Keep in mind that this can also change the font used for text on the page.

So what happened to Student Grades? If I click anywhere within the pie chart and the select the checkbox next to Student Grade in the Fields panel as shown below, Power View creates dividers in each of the schools that represent ‘sub-slices’ one for each grade in the school.

Unfortunately these sub-slices are not labelled so it is not possible to determine visually which slice belongs to each grade. Suppose we went back to the first table in this worksheet and added the Student Grade column back in as shown below.

Now the table includes a row for each grade with the number of students in that grade. At first you might say that did not help much.

However, if we click on any of the sub-slices in the pie chart, the table automatically filters to the information for that sub-slice/grade as shown below.

Ok, I know we have a lot more to learn about Power View and over the coming weeks, I will attempt to introduce you to its many other features. I also know that the charts created today were not that dissimilar to charts and table you could create with PowerPivot tables and charts. In fact, those tables and charts have a greater degree of formatting flexibility. However, we have to walk before we can run. In future segments, I will show how to create Power View charts that you could not create before.

Until then, C’ya!

Can You FILTER() That Down For Me

The last several weeks I have been looking at how PowerPivot in Excel works with Row Context and Filtered Context. I showed that most column expressions use a row context while measures use a filtered context although I could add and remove filters using certain expressions that allowed me to define a filter as a Boolean expression in one of the parameters. Last time we even looked at how to remove the filters by using the ALL() function. This time I will explore the FILTER() function which allows me to define a permanent filter condition to a measure no matter what dimensions or slicers the user chooses for the pivot table. In fact, in the case I am going to show you today, I need to do this because I need one measure to use all filters defined by the dimensions in the pivot table, and I need another measure to obey those filters plus one more.

Again I will use my Contoso data model that I’ve been using for all the examples in this set. I want to look at the number of orders that have returns and compare that to the total number of orders. I initially will want to show this information by sales channel and year/month. However, once I have my pivot table defined, I could of course change the dimensions I want to explore.

Let’s begin with a basic Sales pivot table as shown below.

I built this table using my basic data model with no additional calculated columns or measures except the calculated column in the date dimension that I use to order the name of the months correctly. I can use any of the columns in the FactSales table as my value field as long as I change the aggregate function from SUM to COUNT. By default, Pivot tables assume that numeric fields are summed and non-numeric fields are counted. But as long as I change the aggregate function for numeric fields to COUNT, I will get my desired results. I also modified the formatting to get rid of any decimal places and to add a thousands separator. Other than that, I did nothing special to build this table.

However, now I am going to return to the FactSales table and add a simple measure to count the total number of sales. The expression I will use is shown in the following figure.

I use the COUNT() function which has a single parameter, the name of the column I want to count. Again I could choose any column, but I chose the column [ReturnQuantity]. I will come back to format this measure in a moment, but you can see that the count is a little over two and a quarter million sales records. In fact, I know that this is correct by simply looking at the number of rows in my FactSales table.

Next, I want to count the number of sales records that have returns. This I can do by comparing either the [ReturnQuantity] column or the [ReturnAmount] column to 0. Only sales records which have values greater than 0 for these two columns represent orders which had returns. How can I do this?

One way I could do this is to use the SUMX() functionwith a second measure named [ReturnCount2]. This function has two parameters. The first parameter must be a table and second parameter is an expression of what I want to count. So I might think that I could do something like the following expression:

ReturnCount2:=SUMX(FactSales,IF(FactSales[ReturnAmount]>0, 1, 0))

The theory is that I want to compare the column [ReturnAmount] to 0 and if it is greater than zero to add one to my ReturnCount2 value. I cannot simply sum the [ReturnAmount] because this column represents the dollar value of the return. Nor can I use [ReturnQuantity] because the buyer may have returned more than one of the item from the order and summing the quantity would over count the total number of orders with returns.

I could also use the COUNTX() function. However, if I simply replace SUMX() with COUNTX(), I will get the total number of orders in the FactSales table because COUNTX() will could all non-blank rows. But I can trick the IF() into returning a blank for orders without returns by using the following expression:

ReturnCount2:=COUNTX(FactSales,IF(FactSales[ReturnAmount]>0, 1, BLANK()))

But both of these solutions used the entire FactSales table. There is one other way I want to show you today. I can use the FILTER() function to apply a filter to the FactSales table to return a subtable that only has rows with returns by using the following expression to return a table

FILTER(FactSales, FactSales[ReturnAmount]>0.0)

I can now replace the first parameter in COUNTX() with this FILTER() result which is a filtered table. I can then use any column in FactSales as the column I want to count. Well, almost any column. Actually, I cannot reuse the [ReturnAmount] column which is used in the FILTER() expression because this confuses DAX, but as I said before, I can count on any column in the table. Therefore, my [ReturnCount2] measure expression is shown below.

In this image you can see that I already formatted my measures as numbers without decimals but with thousands separators. Why do I format the numbers here? Simply because it saves time from having to format the numbers in each pivot table in which I use the measures. If I display these two measures in my pivot table side by side, I can see the total number of order by channel in each month along with the number of orders that had returns.

Suppose I wanted to show this information to management and rather than look at the raw counts which could take a bit of time to interpret, I decide to calculate the percent of orders that have returns. I can create a third measure as shown in the following figure that uses the results of the first two measures. I can then format this measure as a percentage prior to using it in my pivot table.

Returning to my pivot table, I remove the counts which I no longer need to display and replace them with the [Percent_Returns] so that management can quickly see that Catalog sales result in the most returns and Store sales in the least returns. Returns do not vary greatly by month, something that I will leave up to you to explore with a Pivot Chart.

Well, I hoped you learned some new ways to apply different filters in your measures from this discussion. C’ya next time.