Welcome back to day nineteen of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day eighteen? If so, here is the link to refresh your memory.
For the next few blog posts we are going to discuss taking Power BI to another level. We are going to discuss drill down (or up) and drillthrough.
Let’s explain the terminology first!
With drill down, we can go from a high level on a report like Year to Date Sales to the lowest detail that we wish to show. We can decide how many levels of drilling. So let’s say our first level is corporate-wide so we might see one row for 2017 that has sales data totals for the entire corporation. If we drill down to the next level, we can see that inside the corporation we now have totals by global regions. If we drill down a little further we might see totals by country and then totals by smaller regions and so on. Drilling up is simply going back up that chain. This adds an entirely new dimension to our reports that eliminates the need for multiple static reports.
Drillthrough, on the other hand, provides the ability to drill through to another page or report for detail and thus filter by the field that you selected when you chose drillthrough. For example, when we have our drill down and we get to the country detail on our report mentioned above, we could drillthrough to a second report that shows sales details and it would then filter by that country.
If this sounds a little confusing, do not fret…we will go through it in the next couple of posts. Stay Tuned!
Welcome back to day eighteen of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day seventeen? If so, here is the link to refresh your memory.
Let’s look at visuals today. The hardest part to designing a Power BI report is deciding which visuals to use that will best convey the message of your report or dashboard. I could go into a detailed analysis of each of the default visuals, but a few days ago I came upon the following visual reference from SQLBI.com (which is an excellent and highly recommended Power BI reference site) and thought I would pass it along instead of reinventing the wheel:
The page where the image was found (http://www.sqlbi.com/ref/power-bi-visuals-reference/) has a download option for the PDF for your daily use. Enjoy!
Welcome back to day seventeen of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day sixteen? If so, here is the link to refresh your memory. Now that we have some insights loaded into our dashboard on PowerBI.com, we can use the Q&A feature of Power BI. What is Q&A?
It is just what you think it would be: question and answer, only this time your Power BI dashboard end-user asks the question of your data and the software returns an answer!!!! This sounds like some straight-up Skynet Terminators taking over the world science fiction, right? Well, of course, we are data people so now WE HAVE THE ULTIMATE COSMIC POWER!!! OK, maybe not that cool, but still!
Let’s dive in and take a look! In Figure 1, we see our original dashboard and if you look right above the pinned insight “G and PA” you can see the “Ask a question about your data” field area where we can begin typing. Once you click into this field it will show you a different screen without the pinned visualizations and will show you some sample columns to select or you can begin typing your question.
In essence, Q&A provides answers in the form of charts and graphs to natural language questions in real-time type-ahead fashion. Watch the progression in Figures 2 through 5 as we type ‘hr by year’ as our question. Also, notice that typing HR first gives us a 755 HR total in the chart responding to our request. Remember that our dataset is called Standard Batting so you can see that Figure 2 shows us the HR column of that dataset as a selection. This is useful if the column was found in multiple locations.
We’ve now successfully utilized the Q&A feature of PowerBI.com. If you did not see that feature, make sure that you have it turned on as shown in Figure 6 below. Stay tuned for the next day in this series!
Welcome back to day sixteen of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day fifteen? If so, here is the link to refresh your memory.
Now that we’ve uploaded our report to PowerBI.com, we can take advantage of a neat little feature called Quick Insights. From the Reports screen, select the light bulb icon and generate the insights as shown in Figure 1.
This generates some helpful and interesting insights from our dataset. However, some of them might be useless because we know our data and might lead to a “buyer beware” situation. In addition, some of them are obvious to baseball fans, such as Figure 2, but with your data maybe you want a correlation pointed out in the data. We generally know that the more games you play, the more plate appearances you should have. But now we have data to back up the correlation!
There were some interesting insights provided such as Figure 3. The tool found a correlation between walks (BB) and age for a certain subset of data: when Hank Aaron played for the Milwaukee Braves. Is that because he became a better, more patient hitter?
In Figure 4, we have an additional correlation, between Strike Outs and Walks (BB) for the Milwaukee Braves. Fascinating!
Keep in mind that there were many insights generated for our data set. Many of them were not useful in this example such as the number of times Hank was caught stealing by the position he played. Because we know the data, there really is not a useful need for this relationship. But we get to decide which insights are useful and save those to our dashboard by clicking on the push pin icon in the upper corner. This will prompt us to select or create a new dashboard, as seen in Figure 5. We will explore dashboards further in day seventeen. Stay tuned!
Welcome back to day fifteen of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day thirteen (as we updated on fourteen)? If so, here is the link to refresh your memory. Today we will publish our Power BI Desktop visualizations to a the Power BI service in order to share with other users.
The first step to doing this is creating an account on PowerBI.com. Don’t worry, there is a free account which lets you publish up to 1GB of data, share dashboards, and have access to cloud sources. If you need more than that, pricing information can be found here.
On the top ribbon, select the Publish icon. You will then be prompted for your PowerBI.com account and password as shown in Figure 1.
At this point, the report will be published to the Power BI service as shown in Figure 2 and 3. That was easy, right?
Our report is now in the cloud! Are you excited yet? I am! Stay tuned to see where we go next!
Welcome back to day fourteen of our thirty-day series on Success in Power BI! Today we will take an opportunity to switch gears as this morning I got a notification on my desktop to update my Power BI installation. This is one of my absolute favorite features of Power BI: MONTHLY UPDATES with new features!
Being a consultant for Innovative Architects, this makes my job much harder because I may tell a client one thing in March and then that is incorrect in April. I am perfectly OK with that because: NEW FEATURES!!!!
So if you are following this blog in real-time, you are going to want to update your Power BI as well to the March 2017 version to prevent any incompatibilities with my version. Look at the features and improvements in Figure 1.
Check out the last item on the list…split column by delimiter. Does that sound familiar? It should as we did that manually using calculated columns in Day Ten. This is the perfect example of what I referred to earlier at IA. I may have told a client that you could not easily split a column by delimiter. Now you can. Isn’t that fabulous? Thanks Microsoft! Below is the video from them on the new features.
Stay tuned to see where we go next!
Welcome back to day thirteen of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day twelve? If so, here is the link to refresh your memory.
While we were away, I tidied up the slicer that we created yesterday with a nice title, a border, I changed the fonts, etc. In other words, I tried to make it match the format of our page as shown in Figure 1. We also selected the Select All Option in the slicer so that we could return to the original functionality of the visuals before the slicer was added. It feels like the same visual theme in our slicer.
Now we want to add another slicer. Select the home run (HR) field from the Field pane on the right hand of the screen. Select the Slicer from the Visualization pane to turn the new bar graph into a slicer. Select Dropdown and configure it to match the first slicer as mentioned above and shown in Figure 2. Select the ellipses (…) option above the selected slicer object and select Z->A Sort by HR to sort the home runs from most to least in our drop down. Notice that the two slicers are related and limit your options compared to the choices you make in the other slicer. This also filters all of the visuals on your page. This is cool, powerful, and sometimes terribly awkward.
Now let’s go for broke and add a third slicer. Repeat the above steps using the team (Tm) field only this time let’s leave it as a list of check boxes since Hank only played for three teams. As you can see in Figure 3, we’ve selected the Milwaukee Braves (MLN) and the Milwaukee Brewers (MIL). This filters our other two slicers to rows of data that only matches those two teams. We could at this point slice these down further and this isn’t really the best example for when you would actually need three filters in a visual but I hope it helps you to get a sense that they are all connected and all control the visuals on the page. In other words, all of them are connected and feed off each other. It is unlimited cosmic power, sort of. Use it wisely. Stay tuned for our next adventure!
Welcome back to day twelve of our series on Success in Power BI! Here is the link to refresh your memory on yesterday’s work on our Hank Aaron project. We now have two visuals on our page and today we are going to add a slicer. Some of my favorite meals came from slicers…wait that doesn’t seem right. Well in that case just ignore Figure 1.
Just like delicious cold cuts, the Power BI data slicer is used to take a large amount of data and make it smaller. In this case we use slicers to filter data, in other words, to get to the heart of the data. Make sense? It is a visual filter made available on our page to filter through the data in real time. Exciting! Let’s pick up where we left off….remember when I said we needed some extra space at the bottom of the page? Good, you’re now ready to begin if your page looks like Figure 2.
- Click somewhere in the white space at the bottom of the page to ensure that neither of our visuals are selected and then select the Age field in the Fields pane on the right hand side. This will default in a nice little clustered column chart (Figure 3) where it adds the ages from each of our records to give us a grand total of 713. This data is useless as this is not a field to sum up. But we need it here, for now.
- With that visual still selected let’s choose the Slicer in the Visualization pane as shown in Figure 4 using the blue arrow. Our clustered column chart has now changed to a list of check boxes representing each age of data in the data set.
- Check some boxes and watch how the TWO visuals change. We are seeing his totals move depending upon the age. This. Is. Awesome!
- Hold the Ctrl button and select multiple ages and watch the visuals change as shown in Figure 5. Notice the changes in the line and clustered column chart. You can actually see that some ages are missing by the gaps. Even the trend lines move. We must be careful with this to prevent a misleading representation.
That list of check boxes is really unnecessarily expensive in page real estate terms. We can change that into a drop down to prevent losing our valuable visual space. If you hover over it on the right hand corner you will see the word List with a down arrow. Select that and choose Dropdown. Our list is now much smaller as shown in Figure 7. What will we think of tomorrow? Stay Tuned!
Welcome back to day eleven of our thirty-day series on Success in Power BI! Need to refresh where we left off from day ten? If so, here is the link. Today we are going to add a second visual to our page.
In order to do that, we need to add another column as these statistics track hits (H), doubles (2B), triples (3B), and home runs (HR) but they do not track singles (1B). I’ve often wondered why when you are looking at a player statistics, the single was relegated as pedestrian to not be depicted in its own column. However, we are going to give it its proper due.
- Go to the data pane on the left hand ribbon.
- Right click somewhere in the data and select Add Column. I wanted to point out that you can skip step one by right clicking on the Fields pane on the right hand side and simply choose Add Column there as well.
- Replace ‘Column =’ with the following (as shown in Figure 1): 1B = ‘Standard Batting'[H] – ‘Standard Batting'[2B] – ‘Standard Batting'[3B] – ‘Standard Batting'[HR]
Now we are prepared to create a pie chart, but first we must make some room and size down our existing visualization on the page leaving some white space. Click on the white space so that our existing visual is not selected so when we click on the Pie Chart visualization it adds a new visual and does not change our existing line and clustered column chart to a pie chart. Click on the Pie Chart in the Visualization pane as shown in figure 2 with the red arrow.
If you notice under the Visualization pane, we have a blank canvas with no fields dragged into fill the pie chart. Select the singles (1B), doubles (2B), triples (3B), and home runs in the Fields pane on the right hand side. This will insert them as values and populate our pie chart as shown in Figure 2. Note that I’ve already made it a little prettier with a change in colors and title. I’ve also added hits (H) to the Tooltips section so that when you hover over home runs for example you can also see how many total hits for example. The hover automagically adds a percentage so it is nice to see what the percentage was based upon.
Notice the white space I left at the bottom, that is for tomorrow’s post. Stay tuned!
Welcome back to day nine of our thirty-day series on Success in Power BI! Have you forgotten where we left off from day nine? If so, here is the link to refresh your memory.
We are going to switch gears today and manipulate some of our data set for future lessons. We have the Awards field currently which has a few different awards delimited by comma. We want to separate out this data into separate columns to report on.
- Go back to the data view (on the left hand ribbon middle icon).
- Right click anywhere inside the grid and choose New Column (as shown in Figure 1).
- Type in the following (replacing Column = as shown in Figure 2): AllStar = IF((FIND(“AS”,’Standard Batting'[Awards],1,0)) > 0,”Yes”,”No”)
- Repeat steps two and three: GoldenGlove = IF(FIND(“GG”,’Standard Batting'[Awards],1,0) > 0,”Yes”,”No”)
This language is called DAX or Data Analysis Expressions and it is a library of functions and operators that are used to build expressions and formulas for Analysis Services (SSAS), Power BI Desktop, and Power Pivot for Excel. Here is an MSDN reference for DAX. And here is a quick reference that I refer to in order to know which function will fulfill my needs.
To summarize what we did above in the steps: we added two new columns called AllStar and GoldenGlove. We looked at the Awards column for AS or GG and used some DAX functionality to return a Yes or a No into these separate columns. Now these awards have more meaning to use other than the long string of awards for that year. Stay tuned to see how we use them!