Calculating Percentages in PowerBI

Last time I showed how you can pivot a multivalued column from a survey downloaded from SharePoint to Excel. After some transformations, I created the following report that shows the number of career responses in each of the 18 categories. While interesting in of itself, it would be more interesting to see how the career choices break down by different dimensions such as the school or grade level, the gender of the student, or perhaps even by individual school.

In my report page, I am going to reserve an area along the left side of the page to list several of the dimensions I’m interest in and to display the total number of students that took the survey along with the number of students in the filtered subset when I filter by one of the dimensions. Basically that means building something like the following:

This image actually consist of four separate visualizations. The top visualization for Total Students uses the Card visualization and initially displays the count of the ID column in the survey table that has one record for each survey. I duplicate this visualization and drag it below the first one. Then for the top visualization, I open the Format option panel in the visualization section and make the following changes:

  • I turn off the Category Label
  • I turn on the Title and then open the Title section to add the Title Text: Total Students
  • I change the font size and color to make this information stand out.

I then repeat the process for the second visualization but with the Title Text: Sampled Students because this count will represent the number of students included in the visualizations taking into account any filters applied to the page.

I then add two single column tables, one for gender and one for grade level. Because I want to use the values in these table as page filters, I change the visualization of these two tables to the Slicer.

Along with the table I created in my last blog, I can work with the slicer values to explore how the career choices change based on gender and grade level by clicking on the check boxes. When I do this, I see that the chart automatically adjusts the columns based on the changes I make to the slicers. Also the number of sampled students in the second card visualization displays the count of students included after applying the slicer. Unfortunately, the total student count also changes. This I do not want. I want the total number of students to always represent all the students in the entire survey.

I can achieve this goal with a little DAX and a custom measure back in the data page for the survey table. The custom measure needs to count the IDs for all the records in the table ignoring any filters applied to the report page. I can do this by passing the survey table name to the ALL() function. This function ignores all other page filters. Then I use the COUNTAX() function which defines the data source as the output from the ALL() function and then performs a count the number of IDs. While this may sound complicated, it is as simple as the following equation:

Notice that I name the measure Students. I must provide a unique measure name for each measure I create. However, I can then use that measure in any visualization such as the card visualization for the total students in the survey.

Back on my report page, I select the top card visualization (for Total Students) and change the field used from ID to my new measure, Students.

Now if I select any of the values in the slicer visualizations, my sampled students card displays the number students included in the filter while the total students card displays the total number of student surveys taken as shown below.

I then add on the right some additional column visualizations to display other data fields such as which subject the student finds most interesting in school or charts that display career choices by gender, by grade level or by other criteria from the survey. Each of these charts begins with a simple table visualization in which I add the columns I want to use. I then convert the visualization to a column chart.

In the image below, you see the final result of the first page of my report. Notice that I also added a vertical line shape to separate the two card visualizations and the slicers from the other column charts.

Since each student was allowed to select one or more careers from the list of possible careers, the total number of career choices is significantly larger than the number of students. In the above figure, the count of career interests, if I were to add the values in each of the columns, would total the number of career selections which is over 17,000, not the number of students. Therefore, I might decide to display the same charts as a percent of all the students rather than a count of all career selections.

Again I need another custom measure to calculate the percent based on total students. Fortunately, I already have a measure that calculates the total student count ignoring any slicer selection or page filter. Therefore, I can generate a percentage using a formula similar to the following:

With this formula, I count the number of surveys filtered by the visualizations and slicers on the page divided by the total number of students who took the survey. The maximum value of this percentage would be 100% if all the students who took the survey selected the same career, such as computers, as one of their choices. Similarly, the minimum value would be 0% if no student selected a specific career as one of their choices. Because students could select more than one career of interest, the sum of the percentages of each of the columns will not add up to 100%, but some value greater than 100%. (Lesson learned: Next time ask for their preferred career choice, then their second career choice and finally their third career choice.)

Next I take the first page of my report and duplicate it by right clicking on the page tab and selecting the option to duplicate the page. (This is a lot faster than recreating the same visualizations on a new page, isn’t it?) On the duplicated page, I modify each of the column charts to display the Percent measure just created rather than the count of ID.

Why does this work? Well, because each student can only select any specific career one time even though they may select two, three, or more careers, I can simply count the number of filtered students in each column by the total number of students in the survey. Each chart already divides the students by career choice, one career for each column. Then additional filters from the slicers may limit the gender or grade level or both. Therefore, I can count students that match all those filter criteria and divide by the total student count to get a percentage of students who have an interest in that career. In fact, this measure also correctly calculates the percent of students interested in each school subject (of which they can only select one subject each) which I can verify by summing these percentages from the chart in the upper left and getting a total of 100%.

The following figure shows the percent page of my report.

I can then proceed to add other visualizations if I want on additional pages. But I’ll suspend this example at this point for now.

C’ya next time for more exciting ways to use Power BI.

Spring Cleaning Time for SharePoint Sites

We have been deeply involved in a cleanup of our SharePoint portal plan for the last several weeks and I thought I would share with you some of our observations because I know most of you with both Internet and intranet sites that are more than a few years old probably share the same issues we do. Our Internet and intranet portals have been around for 9 and 8 years respectively and over time, a lot of ‘junk’ has accumulated. The example I like to use is that it is similar to the way your email slowly fills up with ‘junk’ over time. For example, if you received 20 emails a day, but only have time to address 19 of them, you may go home feeling pretty good about the amount of work you got done. However, that 1 email you did not get to if you consider have only 1 email that you do not get to each day over the course of a typical work year can amount to over 250 emails. Of course, if you get more than 20 emails and the number that you do not get to is greater than 1, that total can expand much more rapidly.

So how do you get a handle on the problem? You could simply delete anything older than 2 weeks old, but that might delete important email messages that you really need to see. On the other hand, the email that you received yesterday and did not get to may be a total waste of time. Clearly the age of the email is not the only deciding factor. Maybe you choose to delete all emails from people outside of your department. Unfortunately, some of those emails from other departments may be more important than the email that circulated around asking people where they wanted to go to lunch on Friday. You could delete anything that comes from outside of the company. That would certainly help keep you focused on your work, but you would also miss notifications of appropriate training or white papers relevant to your job.

Thus you can see that cleaning out your inbox can be more complex than any simple rule or a set of rules (although they may help). It is also important to perform that cleanup on a more frequent basis than once every year. One a month may not be too often and even once a week perhaps on Friday afternoon as you are winding down for the week might be a good choice.

In a similar way, if you have both an Internet and intranet portal (hey, even if you only have a collaboration site), periodic cleanup is still something you need to consider doing. If nothing else, cleaning out old obsolete content will make search run faster and return more relevant results. So here are a few tips that may help you perform your own portal cleanup.

10 Steps for site owners to consider when performing their next portal cleanup:

  1. Remove obsolete or unnecessary sites – Sites where all content pages/documents have not been updated for 2 or more years are candidates.
  2. Examine all pages for duplicate or obsolete content and update or remove – This could result in removing the page itself if all content on the page is obsolete and removed.
  3. Remove obsolete/duplicate documents/files – Multiple instances of files all get indexed and results in bloating the search results with many invalid entries that do not point to the most recent data. Delete obsolete/duplicate files.  Burn copies onto a DVD if you want to keep them.  Adding them to your intranet site or collaboration site is not a valid solution.
  4. Remove content that appears on other sites for your organization that you do not own – Copying/duplicating content that appears on other sites within your organization bloats search results and diminishes the relevance rating of the correct document if multiple occurrences exist.  Any content not ‘owned’ by the department should be removed and replaced with a link to the content on the ‘true’ owner’s site.
  5. Remove content found on sites outside your organization – Not only is this a potential copyright issue, but updates made to the content on the ‘true’ source will not be reflected in the copied content resulting in misinformation.  Just link to external content.
  6. Clean out your calendar/announcements – If your site has a calendar or an announcements list, clean out old events that are no longer relevant.  This will improve the performance of the calendar and/or announcement list.
  7. Consolidate sites – Sometimes subsites were create when all that was needed was another page on the site that owns the subsite (parent site).  Unless the subsite requires a different set of permissions (owners, content managers, approvers, etc.) you may be able to simplify your site structure by moving content/pages/documents up a level.  This will also improve navigation and reduce the number of clicks to find the content you need.
  8. Remove content that really does not need to be public – For any content item (subsite/page/document) ask yourself if anyone in the public really needs to see this content on a public web site or whether it just clutters the public facing sites with content that no one really looks at.  Perhaps all you need is a ‘Contact Us’ link for anyone in the public to request additional information if necessary.  Some current public content probably should only be internal intranet content.  If so, move it there if it does not already exist and delete the public version.
  9. Do not duplicate content between the Internet and intranet – If the content needs to be seen by both the public and organization’s employees, place the content on the Internet and only add links to that content from the intranet. Don’t place the content on both and definitely don’t place the content only on the intranet.
  10. If content is not owned, remove it – If you have current Internet content that is not officially owned consider removing it.  Content that is not owned probably is not updated.  If the content is necessary, an owner for the content must be identified.

Well, that’s it for this time. C’ya.

The Sky is Not Falling

This week an article was published on CIO.com by Jonathan Hassell entitled, ‘7 Things CIOs Should Know About SharePoint Server 2016’. This article found it way circulating around our company from several vendors hoping to convert our SharePoint portal over to their system. They point to various statements about the new SharePoint as evidence that it is time to switch from SharePoint now. I call this ‘The Sky is Falling’ syndrome. Vendors of third party products are using it specifically to freak out SharePoint users to get them to switch to their company’s product.  However, the truth is rather different for existing SharePoint users.  Here is my personal response to some of these statements.  Don’t fall for ‘The Sky is Falling’ sales tactics.

  1. SharePoint Server 2016 might be the last version of SharePoint designed to run on premises.

    Jeff Teper just took over SharePoint at Microsoft last year and has been rebuilding the SharePoint team from the disaster it was in 2014 and early 2015.  At the European SharePoint Conference and the Microsoft MVP conference in November of 2015, a very different picture of the future of SharePoint emerged.  He has not said that Microsoft is abandoning SharePoint.  However, the definition of ‘last version’ is muddy. I believe it is more like what Microsoft has said concerning Windows 10 being the last version of the OS for PCs.  Really?  What does that really mean?  It means that all future updates will be done through regular updates/patches/etc. Haven’t they already done that with Windows 10?  In fact, the Release Candidate for SharePoint 2016 was released, I believe, as a patch to test this capability in SharePoint.  It updated our Beta 2 installation without shutting SharePoint down or reinstalling everything.  Last release?  I guess that depends on what you call a release.  It may be the beginning of the end of release numbers as we once knew them. It is a very different release world coming about.

  2. There will no longer be a free version of SharePoint for smaller shops or department use.

    True, but that is because the free release was collaboration only and that functionality has moved to SharePoint online as part of select Office 365 subscriptions where they call it Team sites.  You can also apparently buy SharePoint online as a standalone offering for $5.00 per user per month. Note that like WSS 3.0, these sites are meant for internal collaboration, not for the creation of public facing web sites.

  3. Excel Services has vanished, and to get Excel functionality, you must use Office Online Server

    Again a good choice to provide the latest updates as soon as possible.  Excel is an office product and will be baked into the Office 365 subscriptions.  Also Microsoft is pushing a new product for data analysis called Power BI which appears to be part of select Office 365 subscriptions although you can get a desktop version for free.  Gartner recently ranked Microsoft’s Power BI as most innovative in the upper right quadrant in their recent Magic Quadrant for Business Intelligence and Analytics Platforms.

  4. Managing SharePoint from the command line is pretty much a PowerShell-only affair these days

    This was announced years ago as a goal for all Microsoft servers.  There is better control over administration of Exchange and even standard Windows servers using PowerShell.  Our team has been honing our PowerShell skills for at least the last 4-5 years because we knew this was coming.  Also the SYSADM functionality was awkward at best. For some time, you have been able to install a Windows Server as a Server Core only version that skips the GUI to provide better performance and a smaller footprint which uses PowerShell for administering the server.  This direction for servers is not new for Microsoft. They gave substantial warning that administrators need to learn PowerShell in the near future.

  5. The migration process to SharePoint 2016 will be a bit involved, depending upon from where you are starting.

    While it is true that out of the box that migration from anything other than SharePoint 2013 is not directly possible, there are tools companies like ShareGate, Metalogix, and AvePoint to name just a few that support migration between SharePoint 2010, SharePoint 2013, SharePoint 2016, and SharePoint online so this is a non-issue.

  6. The Microsoft workflow and forms solution InfoPath is now Moribund

    Microsoft has promised support for InfoPath until at least 2026.  However, I do not expect Microsoft to completely eliminate support until they or a third party comes up with a viable alternative and path to get there.  Their attempt at a different forms solution in 2014-2015, Forms on SharePoint List (FoSL) failed and was removed from SharePoint 2016 until a new solution is obtained.  Unfortunately, they released the news that InfoPath was going to end prematurely without that replacement solidly in place.  In the meantime, there are other third party forms tools like K2 for SharePoint which provides a forms and workflow alternative that might be a reasonable alternative if you feel you need to start converting to something else now.

  7. There is a big focus on hybrid connectivity in SharePoint Server 2016.

    Very true because the vast majority of companies that responded to one survey expressed an interest in a hybrid approach in the future, the bottom line is that today 49% of a recent survey from Rencore use SharePoint Server on premises only, 24 % use SharePoint Online only and 25% that use a “Hybrid” configuration. Because 64% of the survey respondents also use Office 365 currently, I suspect that the percent of hybrid might increase in the near future. One factor that will keep on premise going for some time is the need to perform customizations which are easier when you have local control over the server. Over the much longer term, will hybrid on Online continue to grow at the expense of the on premise only installations, probably.

Finally I want to address the removal of public facing SharePoint sites from the Online version of SharePoint. While I have not heard anything definitive about the reasons for this decisions, I suspect the reasons are either technical (branding) or licensing related or both. I suspect at some point, Microsoft will either resolve these issues and add public facing SharePoint sites back or they will create an entire new product that will allow for the creation and support of public facing sites hosted from their online Office offerings.

In the meantime, I will strongly resist the cry that ‘The Sky Is Falling!’ and focus on things that I can control.

C’ya next time.