Formatting your Pivot Table – Part 2

Microsoft Excel 2010 introduced several special formatting features to dress up your boring Excel-based reports.  The first of these features is Sparklines.  Sparklines allow you to create a mini-chart within a cell to show the trend of data in a column or row.  The two primary Sparkline types are Line and Column data representations.  At least these are the two I will use here.  The Win/Loss Sparkline type would be a great way to represent daily stock price gains and losses.

I am going to add two Line Sparklines, one beneath each of the year columns of my data.  I’ll begin by clicking in the cell immediately beneath the Grand Total row in the 2002 column.  Then from the Insert ribbon, I’ll select the Line style in the Sparklines group.

The Create Sparklines dialog requires two pieces of information, the data range to be used in creating the graph, and the cell where the resulting graph should appear.  Yes, a Sparkline can span multiple cells, but that would not be appropriate here, nor is it done by defining a range in this dialog.  Notice that you can enter the cell ranges directly or you can use the button to the right of each of the range definitions to visually select the cells from the spreadsheet.  When done, click the OK button.

Note: You cannot define a multiple cell location range in the Create Sparklines dialog.  You can only specify a single cell even though the label says: Range.  However, after you create the sparkline, select the cell containing the sparkline and several adjacent cells in the same row or column or even a rectangular area and select Merge & Center from the Alignment group of the Home ribbon.

The following figure shows the resulting Sparklines under each of the two data columns.

For the Grand Total column, I will use the Column Sparkline style.  Because we have a relatively small number of data point, the individual columns are well defined and clearly show the relative increases and decreases in the data.  Note that the smallest value in the data range essentially defines the lower vertical axis value of the columns.

My last format change will be to conditional format some of the cells. I want to highlight in red all the months in which the cost of vaccines provided fell below the monthly average during this two year period.   I could calculate the average by using a separate cell and the AVERAGE() function.  However, I can use functions and calculations directly in the definition of the conditional format.

I’ll begin by selecting the cells that I want to format.  In this example, that would be the values for each month of each of the two years.  Then from the Home ribbon, I’ll click the bottom portion of the Conditional Formatting button to view the different formatting options.  Hovering over the Highlight Cell Rules I’ll select the Less Than option from the secondary menu as shown in the following figure.

When you first select Less Than… the Less Than dialog box appears and prompts for the value to compare the selected cells to in order to format those cells.  In this case, we do not want to compare the selected cells to a fixed value or even the value in another cell.  Rather we want to compare the selected cells to the calculated value returned by the following function:

= AVERAGE($I$5:$J$16)

Be sure to include the equal sign or Excel will try to interpret the value as a string instead.

I am going to leave the default formatting of a light red fill with a dark red text and just click the OK button.

This average monthly value over these two years should be 4,739,515 for the selected cells.  Therefore, any cell with a value less than this should be formatted with a light red fill and dark red text as shown in the following figure.

Now you might be wondering, after taking the time making all of these formatting changes, what happens if the user changes the dimensions or if there are hierarchy of dimensions and the user expands or contracts some of the dimensions.  Well, the good news is that the overall formatting of the measure data is retained.  However, the custom formatting applied to a specific range of cells does not expand or contract with changes in the pivot table.  For example, if we were to add quarters under years in our column hierarchy, the quarterly data would be formatted with the global format of currency.  The year columns would still be formatted as general numbers and they would still display the conditional formatting.  If we add more rows to the table, the sparklines would be overwritten.  They will not move down as rows are inserted by the pivot table.  In summary, all custom formatting is potentially at risk. So why bother formatting at all?  The bottom line is that you probably only want to go to the trouble of dressing up your Pivot Table with custom formatting when you are ready to capture it for a report.

Next time, we will look at one last feature with pivot tables, how to hide columns and rows and how to define different value row and column sets for displaying data.

Learn the New Features in SQL Server 2012

My good friend of over 20 years (yes, we started when we were 8) is doing a series for Microsoft on the new SQL Server 2012 features.  Each Friday is a new webcast.  Register for Kevin’s webcast here:

https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032509161&Culture=en-US for the best hour you spent this week (other than reading my blogs 🙂  ).  I’ll be back Saturday.

Are You a Perfectionist?

Are you a perfectionist?  Do you continue to work at a problem until you get it absolutely right, with the most efficient code, executing in the quickest time, and taking the least resources?  Chances are that if you are programmer or a developer, you either are a perfectionist or lean very strongly in that direction.  It is not unusually because the nature of programming lends itself to perfection.  After all, either the application you write is right or it is wrong.  There usually is no ‘close-enough’ answer.  But as a programmer or developer, do you take getting the ‘perfect’ answer to extremes?

Don’t feel that I’m criticizing.  I was a programmer/developer for many years and know very well the lure of perfection in every project I’ve ever done.  However, sometimes just getting the right answer isn’t what gets you up in the morning for another day of pounding the keyboard.  I know what it is like to spend hours trying to find a faster way for the code to run.  In some ways it is as addictive as playing a video game and trying to top that high score even though that high score is your own.  Perhaps this is in part the reason why programmers can finish 80% of a development project in 20% of the time.  While I grant you that sometimes that last 20% of the application involves some very tricky (read that as complex) programming, it is often just to add those last features (bells and whistles) to make the user interface memorable.  But from a project standpoint, sometimes 80% is good enough and that extra 20% is not really as important as other work you could be doing.

It is not the latest programming language or newest development platform or the coolest database that creates the greatest challenge for programmers.  Rather it is the understanding of the cost/benefit of making those final additions to a program that is one of the hardest concepts for programmers to learn.  Part of the reason for this perhaps is that programmers don’t really have to deal with running the program on a regular basis after they finish coding it.  They don’t have an inherent feel for the cost of making those final enhancements that take a month of development time verses the benefit those enhancements have when saving only 5 minutes a month for each time the program is run by the end-user.  This is where a project manager or project lead needs to step in and weight the costs against the benefits and if necessary refocus the developer into other more productive tasks.

Part of this decision also involves clearly understanding the needs vs. the wants of a project.  Needs are non-negotiable features and options in the program that must work and work correctly for the project to be considered successful.  In fact, a project cannot be closed out unless it satisfies all the needs unless a decision is made to cancel the project completely.  The fulfillment of the project needs may make the project run over budget or over time or both.  In this case, wants are sacrificed in order to meet all of the needs with a minimal of additional cost or time.  On the other hand, wants are just that.  Things that the project owner would like to have but can live without but which only get addressed after all the needs have been satisfied.  Obviously the project budget or timeline determines whether any wants get addressed. Unfortunately, decisions to forgo wants often leave developers with a sense of unfinished tasks.

If this happens often, developers may feel that they never get to finish a job and their sense of job satisfaction becomes seriously undermined.  This is where the job of the project manager or project lead is to work with the programmers to help them celebrate the success of achieving all of a project’s needs even if none of the wants are addressed.  Perfection is sometimes just meeting the needs of the project owner.  Another way to look at this is if the project owner wanted a pocket knife, it may be better to supply them with a really good quality, sharp pocket knife guaranteed to stay sharp for the life of the product than to give then a Swiss Army knife with blades that are hard to pull out and which may not even be sharp enough to cut a sheet of Jello.

Speed of performance is another perfection trap.  Several years ago there was a report that took nearly three hours to run.  There were many reasons for this long execution time, but the bottom line is that by changing the way the report would be generated, it eventually tool only five minutes to run the report.  Since this report was run fairly often, the cost of developing the new solution which took about 2 days was clearly worth the 2.9 hours saved each time someone ran the report.  On the other hand, I thought I could get the time down to 2-3 minutes, but it would take another 2 days to do that.  At a savings of only 2 additional minutes, it would take over two years to justify the expense of gaining that last performance boost.  As a perfectionist programmer, I really wanted to do it, but as a more realistic project lead, I knew there were better uses for my time.

Do you have any perfectionist stories that you would like to share?  If you do, include them in the comments.

A final reminder, this weekend is SQL Saturday in Jacksonville, FL.  For details on the session schedule and directions to the event, go to: http://www.sqlsaturday.com/130/eventhome.aspx.

Formatting your Pivot Table – Part 1

This week we will look at formatting pivot tables.  We will continue with the Texas Vaccination data used in the last several entries.  The initial pivot table as shown in the first figure below is rather plain.  The numbers are not formatted.  There are no thousands separators making large numbers hard to read.  The number of decimal places varies from none to two.  Finally, these numbers represent the cost of the vaccinations, but without formatting the data as currency, the numbers could just as easily be counts of the number of vaccinations provided.

First, you need to know that there are two types of formatting pivot data measure data.  The first type allows you to format the entire table at one time.  The second type allows you format specific selected cells.  Therefore, your challenge is to determine which format should be the overall formatting of the data and apply that format first to all cells in the pivot table.  Then by selecting individual cells or groups of cells to customize the formatting for specific needs.  In this case, I want to format the overall table as currency with no decimal places.  To do that, begin by right clicking in any cell in the table and choose the option Value Field Settings from the menu.

The dialog that appears is actually not the dialog that we want because it does not allow us to change the data format.  However, the button in the lower left corner, Number Format, is the option that takes us to what we want.

Clicking the Number Format button displays the Format Cells dialog.  From your other work in Excel, you should be familiar with this dialog.  Select Currency from the Category list and then specify zero decimal places.  Notice that the currency format automatically inserts commas as the thousands separator (the separator is actually defined by your Windows configuration settings).  You can also choose the currency symbol to appear at the beginning of the value.

After setting the format, you will need to click the OK button twice (once in each of the resulting dialogs) to return to the Pivot table.  As you can see in the figure below, the currency format has been applied to all cells in the pivot table.

Let’s say though that we only want the currency formatting to apply to the Grand Total column and row.  The other cells do not need the dollar sign before the number.  To change a subset of the table’s cells, select the cells just as you would in a normal Excel spreadsheet by clicking in one corner and dragging through the opposite corner to select a rectangular area of cells.

Then from the Home ribbon, locate the Number group of options.  In dropdown box, select the overall format type.  Initially this will say Currency because that is how we just formatted the entire table.  However, you might want to select General to return the format to a simple number.  Then you can use the other options beneath the dropdown box to add the thousands separator again and to change the number of decimal places displayed.

The following figure shows how we changed the formatting of just a selected group of cells.

You could also achieve this same result by first selecting the cells where you want to change the format and then right clicking anywhere within the selected cells to display a popup dialog.  From this dialog, selecting either the Format Cells… or Number Format… option will open the same Format Cells dialog we saw before.

With the measure cells (values) formatted, we can now turn our focus to the labels in the table.  In particular, you may not like the labels Row Labels, Column Labels, and Sum of VCost.  These are actually easy to change since they are just labels. Click on any of these cells, Row Labels for example and then in the expression box, simple delete the current label and type in a new label.  In this case, we may simply want to call the row label: Months.

Similarly, I changed the following labels:

Column Labels   –>      Years

Sum of VCost      –>      Total $ Spent

However, when I try to change the Grand Total column label to Total by Month, I noticed that the Grand Total row label also changed to Total by Month.  In fact, these aggregate column and row labels are connected and will always have the same value.  I have to say, this is one of the few disappointing features I have encountered in working with pivot tables.  Unless I am missing something and one of my readers can enlighten me with a comment. Perhaps Microsoft, if they are readying this, can look into this ‘problem’ for a future version.

Although I will not do it here, you can even change the column headers such as changing 2002 to: Year 2002.  (BTW, if you are wondering how I got the months to display correctly in chronological order rather than alphabetical order, please refer back to my blog of November 12, 2011 (https://sharepointmike.wordpress.com/2011/11/12/create-your-own-custom-list-for-sorting-pivot-rows-and-columns/).

You can also format the text for these labels changing the font, font size, and font style.  You can even adjust the alignment using other options in the Home ribbon.

After making these formatting changes, my pivot table looks like the following figure.  In fact, it now looks more like something that I can include in a report.  But we can dress it up even further as we will see next time.

Next week I’ll finish formatting  this pivot table.  For those of you in central to northern Florida, next week is also SQL Saturday in Jacksonville, FL.  I hope to see you there.

Would You Pay Money to Sell Me Something?

Of course you might say.  Isn’t that the whole point of advertising budgets, promotions, and deals?  Sure it is, but I’m not talking about those kind of things.  I asking whether you would pay a fee to walk in the door and make a pitch for one or more of your products?  Again you say sure, I’ll even take you out to lunch or perhaps bring a couple of pizzas and sodas in for your team to enjoy while I demonstrate our latest and greatest products over lunch so it doesn’t cost you any work time.  Or if it is morning, how about some bagels and coffee while we demo our new system?

Well, if you work for the government, these might be considered bribes.  Yes indeed, the people who make the laws know that government workers are so underpaid that a few slices of pizza or a bagel and coffee would be all it takes for you to sell your soul, or your company’s soul, for that shiny new object the vendor is dangling in front of you in their slide presentation.  Maybe that is the way our politicians operate, but I seriously doubt that most professionals in the business world would be persuaded to make a purchase based on a pepperoni pizza and cup of coke.  I certainly would not.

So how can a vendor of a new product get that product in front of an audience without crossing the line?  Actually, it is easier than you may think.  The way I am going to suggest is that vendors actively seek out user groups and events sponsored by user groups and support them.  I belong to a couple of user groups and I’ll tell you from experience that a user group meeting without food, especially if the group meets after normal work hours in the evening, brings much lower attendance than a user group meeting with food.  But who should pay for the food?  Should the user group collect dues and use that money to buy pizza, sandwiches, or whatever it is they want to have at the meeting so hard working people will come to meeting right after work foregoing their normal evening meal at home?

That’s a possibility or at least it was years ago when you stored your programs on punch cards, but collecting dues from attendees at a user group meeting will probably decrease attendance when most people can find the same information in a white paper or through a free webinar on the Internet.  So how can a user group survive?  My answer is to enlist vendors who are willing to help defray the cost of the meeting location and food.  Depending on the expenses of your group, you may only need one vendor.  Others groups may require two or more vendors if the meeting location or food is expensive due to the number of users attending the meeting.

So what should the vendor get out of this?  The user group should be willing to give the vendor five to ten minutes to pitch their product, talk about their company, or anything else they want to say.  They should not be a main presenter for the meeting unless they present a topic that applies generically without focusing on their own product or service.  For example, a consulting company can come in to present how to improve the efficiency of your T-SQL statements as long as they don’t turn it into a presentation about how their product or service is better than everyone else’s product.  After the presentation during a social/networking time, they can pitch their product to whoever is willing to listen, but not during the main presentation.  The same goes for recruiters.  We had a recruiter at one meeting give a ‘state-of-the-industry’ presentation and talk about what makes a good resume and how to answer questions at an interview.  They did not directly promote their services or their company so it was fine.

You might want to come up with a schedule of how much you want to charge vendors and what they get out of it (a mention during the meeting, a 5 minute pitch about their latest product, or the chance to present the main topic).  Most vendors are fairly reasonable to work with.  And if they violate the rules and go into a sales pitch in the middle of a main topic discussion, ask them politely to hold that topic for the networking time and to continue on with the regular presentation.

So what about those companies that want to charge a vendor to come in the door?  Perhaps the answer is to work through local user groups to sponsor the groups in exchange for the ability to talk to the attendees.  Don’t forget to send special invitations to the user group meeting to those companies to which you want to market.  That will help everyone.  It will bring more attendees to the user groups.  It will give the vendors a chance to get in front of a targeted audience who potentially wants to see and hear about their company, product, or service.  Finally, the employees who attend the user group meetings can extend their network of peers and perhaps learn a thing or two that they can bring back to their workplace.

And the best thing is, none of this is illegal.  …Yet.

In full disclosure, I’ve been a big proponent of user groups since my early days in a group named DPMA back in the mid 80’s.  I continue to be active in SQL Server and SharePoint groups as well as a regular attendee and speaker at SQL Saturdays, Code Camps, and SharePoint Saturdays.  I hope to attend an IT Pro Camp later this summer as well.  In fact, two Saturdays from now on April 28th I’ll be speaking at the SQL Saturday in Jacksonville, FL talking about using DAX in PowerPivot.  These events provide a full day of free training and usually include breakfast (donuts, bagels and coffee) and lunch.  These events are paid for by the vendor who will also be there with tables to talk with you about their latest products or services.  Many of the vendor also have their own give-aways as well.

So if you are a vendor, support your local user groups.  We are waiting for you.  And if you are an attendee at one of these events, stop to talk with a couple of the vendors to learn what they do.  You never know when you might need their product or service.

A Measured DAX Expression

In past weeks, I looked at using DAX to create calculated columns and to link tables that required more than one column as the relational link. Last week I looked at the available built-in aggregate measure options that go beyond the basic COUNT, SUM, MIN, MAX, and AVERAGE functions that pivot table users are familiar with.  But even these options do not solve every problem.  This week I return to DAX to show how to create new aggregate measures.  Using the Texas vaccination database I used last week, I want to ask the question, how many unique patient visits have there been each year in each county.

The following figure shows the starting pivot table which shows the number of vaccinations in each year for each county.  Over the two year period, almost three and half million vaccinations were provided.  The problem is that often a patient received multiple vaccinations in a single visit.  So how can we get a distinct count of visits?  We will assume that each unique visit represents a unique individual since the data provided does not include personal information to identify the patients.

You have two ways in which to begin the definition of a new measure.  One way is to click the New Measure button in the Measures group of the PowerPivot ribbon.  The other method is to right click on the name of one of the tables in the PowerPivot Field List  which is visible as long as the focus is within the PowerPivot table and then click the option: Add New Measure.  Since I want to create a new measure in the Patient_Vaccinations table to count distinct visits, I would right click on the table name Patient_Vaccinations.  While either method displays the following dialog, the advantage of right clicking on the desired table name is that the first field in the dialog box automatically assumes the name of the table you clicked on.  Otherwise, you must select the name of the table in which you want to build the new measure definition from the dropdown menu.

You must then define a name for the measure.  The first name is a measure name that is unique across all pivot tables in the current workbook.  The second name is a custom name for just the current pivot table.  Quite frankly, I generally use the same name for both.  In this case, I’ll call the measure: DistinctVisits.

Next we need to define the DAX formula for the measure.  We learned earlier that all DAX formulas begin with an equal sign.  Then as we type additional characters, the tool-tip help displays the possible commands that begin with that letter(s) along with a helpful hint about what the function returns.  In this case, as we type ‘di’ for DISTINCT, we learn that this function returns a one column table in which the distinct values appear in a single column.  The fact that this function returns not a column or list of values, but a table is something to remember.  The reason this is important will become evident shortly.  As we press the Tab key to complete the selection of this function, we see the function expects as an input parameter a column.  At this point the expression builder box contains =DISTINCT(.  If I type the first letter of any of the tables, the tooltip help will display a reference to all the fields in the table.  To count distinct visits, I will use the VisitID field in the Patient_Vaccinatinos table as shown in the following figure.

If we were to close off the equation at this point and attempt to use it in our pivot table, we would get the error message:

ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (8,66) A table of multiple values was supplied where a single value was expected.

A measure must return a single value, not a table or even a column.  Therefore, we have to wrap this calculation inside of another function that can count the rows in a table.  Looking through the available DAX functions, the obvious choice is the function =COUNTROWS() which fortunately accepts a table as input.  Similar functions like =COUNT() and =COUNTA() expect to have a column passed to them, not a table.  Remember a column is essentially a field in one of our source tables from the PivotTable Window. (By the way, the different between =COUNT() and =COUNTA() is that….)

Thus the final expression for our measure is shown in the following figure:

When we press OK, Excel not only adds this new measure to the field list for the selected table, it also updates the PowerPivot table to include the measure as the value (or one of the values) displayed.  In the following figure, we can see that there were 655,212 distinct visits over the two years to receive the three and half million vaccinations.  Obviously, many of the patients received multiple vaccinations within the same visit.

One last thing to note this week is that even though we created our own calculated measure, we can combine it with the build-in aggregations we looked at last week such as % of total or % of row or % of column.  These calculations will now be based on the number of distinct visits rather than the number of vaccinations as they were last week.

While this was but the simplest of DAX measures we could create, the basic concept is the same for all DAX measures.  They are defined not as column, but as expressions added to one of the measure tables so that PowerPivot can use the expression to calculate each cell of the pivot table, not each row of one of the source tables.  In the above example, we had only 3 columns and 7 rows or 21 cells in which the calculation needed to be performed.  A DAX column expression on the other hand is performed on each row of the table which for the Patient_Vaccination table is almost three and half million rows.  This is an example of how DAX can provide superior performance to a pivot table by minimizing the amount of calculations performed.

Manage Up/Manage Down

What kind of manager are you (or if you are not a manager, what type of manager is your manager)?  Do you manage up the organization structure or do you manage down the structure?  Not sure what I mean?  If you are a manager that takes order from the top and simply passes them on to your staff without any thought about whether the order is reasonable, is a good use of resources, asks the wrong ( or right) question, or will cause other projects or tasks to be delayed, you are managing down the organization.  On the other hand, if you use every opportunity to educate upper management on what skills and abilities your group has, how your group can make a difference to the department, division, or entire organization, or how the organization can improve customer satisfaction, retention, or profit return, then you are managing up the organization.

Whether either of these methods is right or wrong depends a lot on the type of management you have.  If your management consists of innovative, creative visionaries, managing from the top-down may be the most efficient mode for a successful company. Many people would label CEOs from companies like Apple and Microsoft with those labels because both Steve Jobs and Bill Gates were very good at sensing what the public would be interested in buying and it was their vision of the future that often guiding many of the new products for each of these companies.

Other companies may have great managers, but they rely on innovation and new ideas to percolate up through the ranks.  Staff at all levels are encourage to make suggestions on new ways of doing current things, on new products that may new needs, or on ways to more efficiently service existing customers.

Either of these company types can be successful.  Companies that fail fall somewhere in between such as companies which have great manager, but not the innovative imagination of their competitors while at the same stifling suggestions, recommendations and new ways of looking at things by their lower staff members.

As the economy improves, you may be thinking about changing jobs or perhaps even changing careers.  If you really want to enjoy going to work, I would suggest that you look for one of these first two types of companies.  Which one depends on your own personality and for what position level you think you can apply.  In a time like this, coming out of a recession, picking the right bus to get on can make a huge impact on the rest of your career as some companies will soar and other may just be absorbed.

Yes, this entry was shorted than many in the past and I’m going to warn you now that they will continue to be a little shorter over the summer months because no one wants to stay inside to read a blog when they can be out doing something fun after work (like cutting the grass, trimming the shrubs, painting the house, etc.).  I thought about going to a one blog a week schedule instead, but decided that shorted, more concise blog entries on a regular basis would probably be appreciated more.  Tell me if I’m wrong.

I’m also going to be hosting a local study group for our SQL Server PASS chapter for the Microsoft BI track over the summer which will also take quite a bit of time, but which may also provide some great technical reads for those of you who are BI curious (that’s BI as in Business Intelligence).  But first I still have some stuff to finish up on DAX over the next week or two in my Saturday posts.

(To become a member of our study group for the Microsoft BI certification in SQL Server, you first need to become a PASS member.  It’s free.  Just go to: http://www.sqlpass.org/.  Our study group requires PASS membership, but it is a virtual group and could include members from anywhere.  We are still getting everything set up to start, so you have time.  Email me for details at: MPA_SharePoint@live.com.

PowerPivot Table No-Code Measures

So far in various weeks we looked at how to create a basic PowerPivot table and even how to use DAX expressions to create additional columns.  We even saw that calculated columns are a necessity when trying to join two table on two or more fields since PowerPivot will only allow join definitions between tables using a single column.  The one area of DAX expressions we did not look at yet is creating new aggregate expressions.  However, before we do that, we need to look at some simple no-code solutions to displaying calculated measures.

The example I’m using here is vaccination data for the state of Texas for the calendar years 2002 and 2003.  I often use this SQL Server database as the data source for my PowerPivot presentations at SQL Saturday events because it is fairly straight forward requiring only a few related tables.   In the following figure, I show a basic pivot table created from this database to display a count of office visits to get vaccinations by racial group and by year.  (Click on image if it is fuzzy to see the image full size.)

Notice that the Visit ID values are huge to say the least, but that is because PowerPivot decided to sum the VisitID values since they were numeric values.  Obviously, we do not want to sum this field.  We only want to count the number of visits.  We can easily change this default action by right clicking on the Sum of VisitID field in the Values box.  The menu that appears has options that would let you move the field’s relative position in the box (if there were more than one field) or to one of the other boxes that define the pivot table.  But what we want here is to explore the Summarize By option which displays a sub menu that includes five choices as shown in the following figure

By selecting Count from this list, we can display the number of visits by race and by year instead of the sum of the visit id as shown in the following figure.

But sometimes a count is not all you need.  Perhaps you want to show the number of visits as a percentage of the total visits in the table or as a percent of the total visits by year.  You might think that you will have to resort to a DAX measure expression to build the calculation, but PowerPivot provides several built-in calculations that you apply without knowing any DAX or performing any calculations on your own.

Right click on any of the fields in the pivot table and position your mouse over the Show Values As menu option as shown in the following figure.  A fly-out menu appears with 14 built-in calculation methods.  Selecting the first one, % of Grand Total compares the value in each cell of the table to the overall Grand Total for all the years and races and reports what percent of that total the current cell represents.

For example, in 2002, 398,759 patient visits were from Hispanics.  Compared to the total of 3,460,679 total visits during the period 2002-2003, this represents 11.26%.

In a similar way, we can display the percentage of total visits each race represents in any given year by selecting the % of Column Total calculation as shown in the next figure.

While I will not show it here, we could also look at the percent each cell in the table represents of the row total.  Ok, just because you can do a calculation does not mean that you should or even that it makes sense in all cases.  In this case, we would be reporting on the percent of visits by each race that occurred in each year.  I’m not sure what that would really tell us.

But lets modify our table a little and show races by county as shown in the following figure.

In this case, we have built a simple hierarchy of data with County being at the top of the row hierarchy and race being a subset under county.  Now we can ask the question what percent of visits in each county are represented by each race by choosing the option % of Parent Row Total.

Now, each race row shows the percent of visits by that race within that county for that year.  Notice that even the county rows are displayed as a percent, but they are a percent of the total visits for the entire year.

Suppose that instead of looking at visits by race by county, we want to look at visits by race by quarter by year.  In this case, we might start with the pivot table shown below:

This time, we would select the % of Parent Column Total as shown in this figure.

Another interesting thing you can do is compare one row or one column to another to get the differences or percent differences.  Lets return back to the basic pivot table which reports visits by race and year.  Right click in any of the data cells in the pivot table and select Difference from in the Show Values as submenu.  This option displays a popup dialog asking for the Base Field to define where we want to calculate the difference from.  In this case, we want to calculate the difference from one of the years in the report so select Year from the drop down menu.  Notice that you can select any of the dimensions used in the pivot table.  Next, select the Base Item or the specific value within the dimension that you want to use as the base.  If we want to calculate the change in visits from 2002 to 2003, we would select Year as the Base Field and 2002 as the Base Item as shown in the following figure.

Notice that two of the Base item values is (previous) and (next).  These options would be useful when comparing the values in any one column to the column immediate to the left or right of the current column rather than to one specific column such as when you select a specific value.

The resulting pivot table shows the number of increased visits in 2003 compared to 2002.  If the visits in 2003 were less than 2002, the value would be negative.

In addition these the options shown here, there are other predefined calculated measures that you can use such as running totals and rank.  I encourage you to explore what these options can give you before you decide to create custom DAX measures.

Well, that’s all for this week.  Next week I look at a few formatting issues before covering how to create custom DAX measures.

See you next week.

Marketing: Science, Art, or Voodoo?

I was digging through some boxes that were still closed up from when we moved to Florida almost 19 years ago.  I was hoping to find some things to pass on to others, donate, or throw away.  Instead, I ran across some old notes from a marketing class I took when I was going for my MBA.

This was back in the days before getting an MBA with your engineering degree was cool.  I was one of only a handful of engineering graduates going for an MBA amongst a swarm of business majors. When we found out that the marketing class would involve a computer simulation of a competitive market, we quickly signed up for the class just to play with the computer simulation.

On the first day of class the instructor split the class into 6 groups of 4 people each.  Each group would represent a company and each company would produce 4 products.  Of course, each company produced the same 4 products.  We would budget our money (play money) by quarter into major areas such as product research, manufacturing, cost of goods sold (reflecting the quality of the materials), salaries and benefits of workers and administration, marketing/advertising, and after sale customer support.  We could budget the money by product as well.  That means we could spend more on research for one product while spending less on another product.  The details of the data we had to supply each week goes beyond the point of this blog.  As a general example through, we could allocate different amounts of the marketing/advertising budget to different media types.  We were also supplied with ‘historical’ statistics on how effective different marketing methods were for different demographic groups and which groups tended to buy which types of products.

Each week at the start of class we would submit our ‘budget plan’ for the next quarter on how we were going to spend our company’s money.  During class (a 3 hour evening class) a student assistant for the professor would take the data from each group, go over to the data center, enter the data onto punch cards (yes, it was a long time ago) and run the program.  After the program ran, he would bring back the printed reports to show how each company did.  It would specify how much of each product each of the six companies sold, your company’s costs, and your company’s profit or loss.

Before I go on, I need to mention that each ‘company’ had to create 3 objectives or goals at the start of the semester for their company.  A goal could be to become the top seller of Product A, or to become the seller with the best total profit returned on Product B, or perhaps even to be the company with the best overall profit margin or the greatest cash reserves.  One group composed entirely of business majors had the goal to the top seller of each of the four products.  An aggressive goal to be sure, but potentially possible.

As each week passed (representing another quarter year of the simulation), most groups made progress toward achieving at least one or two of their goals.  The team that had the goal of being the top seller of all four products however was not doing well.  In fact, they seemed to be consistently in the lower half of the results each period.  We all assumed that with five other teams competing to be the best in only one or two products, that this team was just spreading their resources too thin to be successful with all four products.

Finally, the last day of the class simulation arrived.  We all worked hard to try to meet at least two of our stated goals by coming up with the best allocation of our company resources.  After all, how well we did in the simulation to reach our goals would determine a major portion of our grade.

All during class we hardly listened to the professor drone on about how he did this study or how he devised that successful marketing plan.  So when the student assistant arrived back from the computer center with the final set of results, we could hardly wait to see how well we did.  As we were looking over our results and wondering how we had lost market lead in Product A, we heard from across the room four shouts of joy coming from the business team with the improbable goal of being the market leader in all four products.  Sure enough, looking at the total sales by company showed that they had succeeded in getting the highest market share in each of the product lines.  But class was over for the night and we would have to wait until the next week to find out how they did it.

The following week we had to present a report to the entire class on how we did and what strategies we used.  One by one, each of the other groups presented their results and tried to explain why no one achieved market leadership in any of the products even after showing so much promise earlier in the simulation quarters.  When the last team got up to make their presentation, we soon learned how they had done achieved market leadership in each of the products.  Did they win by science (skill) or art (luck)?

Unfortunately, after hearing what they did, we all felt cheated.  They succeeded in pushing ahead of everyone one else by having a ‘fire’ sale or a ‘going-out-of-business’ sale.  They cut quality, eliminated market research, slashed salaries, fired half their staff, lowered their prices to break even with costs and threw all of the company’s cash into marketing and advertising.  As a result, the program simulated the typically consumer response when looking for a bargain and granted them a huge market share for each product.

Of course, they had no company left at the end.  They had no inventory left, no cash to speak of, and product quality, never better than average, fell to dead last.  So what did our professor of marketing do?  Did he criticize them for bankrupting the company and putting hundreds of virtual employees out of virtual work?  No.  Rather he praised their brilliance in manipulating the simulation factors that allowed them to reach their goals and gave them A’s for the class.  Even the other business majors in our class praised this team for their success even through it was at the expense of their own success (and grade).  The rest of us engineers with B’s and C’s were immediately convinced that it was all voodoo.

In recent years, we have seen an increasing number of executives act the same way in the real world.  Between the crisis in the savings and loan and investment industries, I sometimes wonder where ethics and morality have gone.  Everyone seems to be thinking about the bottom line for the current quarter only so they can use their current success to get their next big job before their last company falls apart and takes them down with it.  No one seems to care about the decimated companies and lives left behind.

I’m not saying that watching the bottom line for the current period is not important.  It is, but not at the exclusion of the long term health of the company and its employees.  Somewhere many business people have lost that perspective.  Yet even today, there are companies that are succeeding while still acting responsibly and serving not just their companies with great products and services, but also providing a great environment for employees.  They nurture a level of loyalty toward their employee’s long term well being and growth.  At the same time, they receive without asking, their employee’s loyalty to do everything they can to make the company great.

Who are these companies?  A few years ago, Jim Collins wrote a book called, ‘Good to Great’ which highlighted a few of them.  In fact, Jim Collins has authored or co-authored several books that attempt to explore why some companies achieve success while others just manage to survive or perhaps don’t.  Jim takes that high road.

If you work for one of the great companies and want to add a note here about your organization and perhaps what makes it great, please do.  On the other hand, if you just want to rant about your company, don’t bother.  I won’t publish your comment.