Power BI Report Card Visual - Snack 14

That little table we made is nice.  It would be better if it were a little more reactive to changing conditions in the report.

What does that mean, anyway?

As mentioned previously, Power BI is not meant to simply display plain old ordinary data in the same plain old ordinary way that Excel does it.  What would be the point of that?  It is meant to offer opportunities for manipulation, interactivity, and self-service analysis.

Let's jump into EDIT mode on our report and I will show you what I mean.

1. Select the table visualization that we created last time.
2. Right-Click
1. Copy
2. Copy visual
3. On the keyboard <CTRL>+V
1. A copy of the table should appear
4. Grab the new visual and drag it out of the way somewhere but keep it selected.
5. In the Visualizations pane,
1. Click on "Card," which looks like a little box with a "1 2 3" inside.
2. It should change your table to plain white box with 127.93M inside.
6. File -> Save

So, what is this showing?

Look over in the Visualizations and see that there is only one item in your Fields section.  It should be displaying the same number as "Sum of Gross Sales," though abbreviated.

Most folks looking at this sort of thing are probably not interested in gross sales though.  Let's go ahead and change to what everyone really cares about, profit!

Make sure you still have that visual selected, and ...

Drag and drop the [Profit] field from the Data pane into the Fields section of the Visualizations pane.

We started off saying something interactivity though.

Click each of the years in your table in turn now.  

The card visual will change to match your selection.

We can look simply at sales as a table, with profit independent of that in another visual.

This enables me to drop [Profit] out of that table now, since we have it showing up somewhere else.

If you want, if you still have the visual selected, you can visit the "Format visual" tab under the Visualizations pane and change the look and feel to fit your taste.

Otherwise, that is all we have today!  I am hoping to get these out a little earlier in the day on Friday, since it has become more of the catch-up-and-learn-things day for the workforce.

Hold on a minute!!!

There is some sort of little triangle with an exclamation point above the card!  

That doesn't look good.

It is not doing any damage.  It is because we copied the table visual to create another visual.

If you hover over that icon, it will tell you what is going on. 

This is a bug Microsoft recently introduced that is a super annoying.  It seems that they failed to recognize that this copy/paste of visuals happens all the time.  I mean, who wants build everything from scratch?  

Looks like you are going to get to "Format visual" anyway.

To fix that little issue, go over to the Visualizations pane and switch to "Format visual."  That is the one with the paintbrush icon over the bars.  Change your "Style presets" to "Default."  It goes away.  The option for "Style presets" go away too.  

Ain't that fancy?   

Don't forget to SAVE one more time.

Oh, and going forward, the image attached will be my final screen.

Power BI Report Question - Snack 13

For any true analytics to occur, we really need a question.

We cannot just give answers to questions that do not exist.

Sometimes, we have to come up with our own questions too, on top of what has already been asked. 

Other times, we are just given a bunch of data and told to make sense of it.

That is where we are now.

Let's start simple.

What are year-to-year Sales totals and what is our Profit from those Sales?

We could answer that with the table we have, but there is entirely too much noise in there.

We should pair that down into something useful that actually answers that question and gives us some space to answer other questions.

Get into your report and jump into EDIT mode. 

Under the Columns in the Visualization pane, you have a list of all of your columns.

If you have not noticed before, there is a little "x" next to each one. That enables you to remove the column from your visualization, which is our table, in this case.

You can also grab ahold of each one of those little boxes and reorder them (drag-n-drop).

Based on what you already know, see if you can figure out how to make yours look just like that in the screenshot.

You will need to ...

• Add a field
• Remove multiple fields
• Change the style on your table.
• Change how a field is displayed
• Save the report.

OK.  Here's what you need to do ...

• Add the Year column to the beginning of your list of columns
• Change the Year column to "Do not summarize"
• Move "Sum of Gross Sales" behind the "Year" column
• Move "Sum of Sales" behind that column
• Move "Sum of Profit" behind that column
• Change the visualization Style Preset to "Alternating Rows"
• File
• Save

If you are unable to figure it out, message me and I will give you a hand.

Planning for the Yellow Stone - Phase 4

 

The next phase in planning is somewhat easy.  We picked up the Annual Pass, since we will be visiting 3 National Parks, at minimum. We just need to figure out what to wear now, and how to stuff it all in the vehicle. Both of those will be a small challenge.   The transport is small, though we believe sufficient

The weather will range anywhere from 0-10 °C while we are there, so everything from our fall and deep winter wardrobe will be required.  Actually, the plan is for jeans and flannel, at least for me.  Oh, and a parka.

The way things usually go, we will need none of that.  It will be warm and welcoming there, while the homestead plunges into the depths of winter.  I know we could not be that lucky though.  

Other points of interest in this phase including checking the general sunrise/sunset and moonrise/moonset.  The later will offer no dark night sky on this visit.  It will be a quarter moon advancing quickly on a full moon, setting around and after 11 pm. 

Power BI Report SUM - Snack 12

Welcome back!  It is time to talk maths for a moment.  There is a bit here that is kind of important to know.

If you are using the same fields I am, you will notice something a little unusual at the top of the Table visual that we put together.  Many of the columns begin with "Sum of ...," instead of simply stating the column name.

What does that mean?

It means that it is summarizing that column. It is not the same as adding, but it is.  It is not the same function as Excel, but it is.

I know, that is not exactly helpful.

Well, it is not exactly easy to explain either.

Hopefully, this example will help.

If I have multiple Sales entries on a certain date, it will only show the total of those entries on that date for all of the attributes chosen.

Still confused?

If we only had the one attribute "Sale Amount?" it would summarize that.  Consider these entries.

    6/1/2014 - $3
    6/1/2014 - $2
    6/1/2014 - $6
    7/1/2014 - $4

Using this data, our table would likely appear as follows:

    6/1/2014 - $11
    7/1/2014 - $4

Of course, if you added another attribute to that, such as Country, it would appear differently.  Consider these entries.

    6/1/2014 - $4 - Canada
    6/1/2014 - $1 - France
    6/1/2014 - $5 - France
    7/1/2014 - $2 - Canada

Using this data, our table would likely appear as follows:

    6/1/2014 - $4 - Canada
    6/1/2014 - $6 - France
    7/1/2014 - $2 - Canada

What if I do not want to summarize that data?  What if you want an Average, Median, Standard Deviation, Variance, or some other standard statistical representation?

Get back into your report and ...

1. Choose "Edit" mode
2. Select the Visualization
3. In the Visualization Pane,
1. Click the tiny down arrow next to "Sum of Sales"
1. A somewhat lengthy menu appears, with a checkmark next to "Sum."
2. Choose "Don't Summarize" and watch the results change.
3. Go back and choose any of the other options below that.
4. Try them all if you like.
1. Keep in mind what was just explained.
4. DO NOT SAVE!

Basically, this is how we define the appearance of our data in the visualization.

Most of the time, we are going to want it to "Sum."

There are times, and types of data, that you do not want to "Sum" or cannot "Sum" without some other calculation.

If you choose any of the columns that host text values, you will notice that they do not offer the option to "Sum."  Those only offer First, Last, Count, or Count (Distinct) and that is but one example.  There are others.

Think about how you would use this with your data, and we will come back to it another time.

Planning for the Yellow Stone - Phase 3

Turning attention to Yellowstone National Park required a whole new map.  The original build apparently contained too many layers.  There were nine in all, one for each day on the road, as well as other layers for those sights we wanted to catch up with.  It is now consolidated, and will be shared along in due time, after the expedition is complete.

We knew generally where and what we wanted to do while in Yellowstone.  The layout of things, time distances and accessibility were points of research for this round. Driving a thousand miles seemed to necessitate some sort of plan of attack though.   

A journey down the YouTube rabbit hole revealed many of the essential things.  Don't pet the bison.  Don't swim in the hot springs. Keep a safe distance from all wildlife, lest you become their punching bag or their next meal.  The above video stood out much more than many of the others, when it came to actually planning a handful of days in the park.  Indeed, it impressed us so much so that we modeled our own coming adventure on it, carefully mapping out the stops of their 3-day adventure and modifying it to fit our locale and timeline.  

We expect that he is very likely onto something here, choosing to stay in a different place every night.  It feels a little late to go through adjusting all that though.  We shall see how things work from one centralized location and report back on that.  The little exercise in mapping out their path definitely helped to firm up our plans though, and we discovered along the way that we might want to considering coming in through southern entrance.  

image borrowed from We're in the Rockies

The decision to do so is really for no other reason than to have a look at the Grand Tetons, prior to entering the Yellowstone valley itself.  There was no sense in arriving by way of the same route we came in and that National Park has been on the list of things to do for just as long as Yellowstone.  The only entrance not covered in our plans is the elusive Silver Gate.  I am sure we will find a way.