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.

Tao Verse 9

 

This is the way of Heaven.  This is the way to Heaven.