Tao Verse 11

when a thing has existence alone it is mere dead-weight

Power BI Formulas and Calculations - Snack 19

Today, let's talk a bit about Calculations aka Formulas, which are called Measures in Power BI.  They are not a whole lot different than their counterparts in Excel.  There are some distinct differences though and too many to discuss in this forum.

Everything done in the report so far is nice but does not address one concern.

    What if the data does not display some calculation or another?  

We have to add it ourselves.

    Do we add it to the original spreadsheet before we connect the spreadsheet?  

You can but it is not quite as efficient.

    What if we do not think about this calculation until after we have already connected it? 

That is what we are about to talk about.

    Can a calculation be added to our spreadsheet after we have connected it to Power BI?

Yes, but it is awkward at best.

The best way to handle this situation is to do nothing with your original spreadsheet.  Best practice is to keep your original data as it was originally sourced and just do a little coding.

    Wait a minute, I did not bargain for that!  

Don't worry.  It is relatively easy, and I am going to tell you exactly what to do.

Let's say for instance that you want to know the difference Sales and Gross Sales.

In Power BI Online,

1) Access the report we have been working on.

2) Choose EDIT

3) Choose OPEN SEMANTIC MODEL (a button on the top row)

The view will change to a little table showing all of the table columns.  There will be a green bar at the top that says, "You are in Viewing mode and changes will not be saved." 

4) Switch to EDITING by selecting the green menu button in the very top right of the screen that currently says "Viewing"

BEWARE! Keep in mind your changes will be permanent and automatically saved. 

5)  Right-Click on the table name in the Data Pane on the right.

6) Select "New Measure"

A new box appears at the top of our space with red check box and a green check box which says "Measure =       "

In the following steps, we are going to put in the following measure:

    Sales Difference = SUM(financials[Gross Sales])-SUM(financials[ Sales])

Pay close attention to what is happening as you type. 

7) In the Measure box type "Sales Difference =" over the top of "Measure =" 

Nothing happens. Hrmm.

8) Type "SUM" then press <TAB>

Notice that as soon as you began to type, a drop-down of options appears.  Those are other expressions that you can use.  We are only going with SUM for right now.

Also notice that a "(" was added automatically for you when you hit <TAB>

9) Type "Gr ..."

The name of that column from the table appears suddenly

10) Press <ENTER>

11) Type ")" to close the argument.

Sorry.  It does not know when to stop, so you have to tell it.

So far, we should look like "SUM(financials[Gross Sales])."

That is not enough to get us there.

12) Type "-"

Nothing happens.

13) Type "SUM" and press <TAB>

14) Type "Sales"

Now we have a problem.  There are two to choose from.  We already used one of them though, so that narrows things down.

15) Click the one titled "financials[ Sales]"

16) Type ")" to close the argument.

17) Press <ENTER>

If you did everything right, you should Power BI "Working on it ..." and then your Measure will appear in that box, without any errors or weird red lines underneath parts of it.  If it has those things, erase it and start over.

If you cannot get it to work.  Grab the full formula above and paste it in that box instead.

18) Switch back to "Viewing" mode by clicking that little button in the top right of your screen that currently says "Editing."

Notice that there now appears to be a column in the table for "Sales Difference."  It is actually only a formula that will consistently calculate the difference between those two columns everywhere you use it.  It will act like a column but really is not.  It is only pretending.

What we have done is tell Power BI that every time we drop in this little pretend column, we want it to calculate the SUM of Gross Sales, calculate the SUM of Sales, then subtract the latter from the former.  Easy, right?

You can now go back to your report and use this little formula anywhere you like; well, most anywhere.  That is another story for another time though.

A couple of other related stories are New Columns and Visual Calculations.

When we first started with this, about the time of the right-click, you probably noticed you can also add "New Column."  That is a whole different situation than what we just did, though it can work much the same, though on a row-by-row basis.

What is the difference?  

Days of argument over which is more suitable for conditions in the report.

Stick with DAX for now.

Oh, I probably forgot to mention, that is what "code" we were writing.

While a lot of others try to define catalogs of the various expressions available for DAX, Microsoft has their own and is probably more reliable than others.

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Learn

You might also discover or someone else may also tell you that you can simply use a "Visual Calculation."

You can.  It is easier.

Understand that they only apply to the visual selected though and cannot be reused easily.

That is the best I can frighten you on this Halloween.  

Happy Measuring!

Power BI Report Titling - Snack 18

It feels like the only thing really missing from this little report is perhaps some sort of title.

Let's add one!

1) Jump into EDIT mode on the report.

2) Click "Text Box" at the top of the screen.

A box appears with an accompanying Format Box.  If you have any experience at all with Microsoft Word, you already know what to do here.

3) Give the report a title by typing into that text box.

4) Choose whatever formatting options make you happy.

Simple, right?

However, suppose for a moment that you or the person(s) accessing the report would like a date on the report that reflects whatever year, month or day is selected.

That little Format Box is more powerful than you realize.

5) In that Format Box, click "Value."

The format box expands so that you can "Create a dynamic value that updates with your data."

This is where you really to know your data and how you might query it to answer the first question in that box, "How would you calculate this value."

It also helps to know how Power BI wants you to ask this question.  Sometimes, you have to play around with the word order to get it to do what you want.  I already did that for you here.

6) Type in that box "Latest Date Financials"

7) An option displaying the very same thing you type should appear below that.   Choose it!

Just below that, where says "Results," you should see "12/1/2014"

Beneath that is a drop-down to choose how to display that result and other associated formatting options.

Finally, the box at the bottom enables naming the value.  This is handy if you have a lot of different values driving that title and/or description and need to keep them all straight.  You can literally write an entire diagnosis in that text box, using nothing but defined values.

Let's leave that last one as #Value for now.

8) Click Save on the Formatting Box.

The date should appear in your text box.

You might need to format it to match any text you placed before that.

9) File

10) Save

Let's test it out.  Click on "Qtr 2" in the bar graph and the date just created should change to 6/1/2014.  If it did not, start over. 

Fun, huh?

You can also add many of the options that visualizations have to that text box. If you have it selected, what is normally the Visualizations Pane has become the Format Text Box Pane.  That is not the same formatting just covered; it is for the box itself.

That is all I got this week.  

While it feels like that is all that is left to do, we still got a few things to talk about, so look for another episode soon.

Power BI Report Filtering Options - Snack 17

There are multitude of ways and means to filter in Power BI.  A few snacks back, we talked about how the visualizations can filter one another. Additionally, specific visuals can be included for nothing more than filtering.  If you scan through the visualizaiton icons, you will find such filter called "Slicer."

Slicer enables you to choose a datapoint or set of datapoints to use in filtering the page.

Let's try it out.  Access your report and jump into EDIT mode.

1. Click out in the blank space so that you are sure to have nothing selected.
2. Find and click the SLICER visualization under the Visualizations tab.
3. Drag and drop SEGMENT from the Data Panel into the Visualization Panel Field
1. You should see the visualization populate with the market segments.
1. Click the boxes and watch the changes
2. Unclick all boxes
4. Format your visual
1. Under Slicer Settings, you will see that this visual has three.
1. Vertical List
2. Tile
3. Drop Down
2. Cycle through them and see what happens
3. Choose Drop Down when done playing around
1. As with other visuals there are other modifications you can make it look just how you like.
5. Move and Size your visual to make the most of the space on the page.
6. SAVE!!

You are probably thinking you can squeeze a lot onto the page and manipulate quite a bit with filters.  

That is correct, but it can get busy, and space is limited.  Eventually you will find yourself lacking sufficient real estate, especially when your users start telling you how they would really prefer to slice and dice your report.

That is where the Filter Panel comes in handy.  This is always present and can be populated in any way for a single visual, a single page, or every single page in the report.  You can guess what they by their section name in the Filter Panel.

Try it out!

1. Click in the blank space of your report again to ensure nothing is selected.
2. Drag and drop the following fields from the data panel to the little box under "Filter on all pages"
1. Date
2. Discount Band
3. Product
4. Segment
3. SAVE!!

There only a couple of parameters you can mess with on these. 

• Require single selection
• This will force the user to choose only one item, though they can still CTRL+Click to choose more.
• Filter type
• Basic filtering
• Offers a list of choices
• Advanced filtering
• Offers a sort of query view enabling more exacting choices
• Relative Date & Relative Time
• Only available for Date fields
• Enables a user to choose a period
• In the last ... however many whatever
• In this ... whatever
• In the next ... however many whatever
• requires futures dates

You can also hide these filters, which is useful if you want to present a very specific view for a visual or for a particular page.  Hover over the cards in the Filter panel and you will see tiny little icons that enable you to ...

• Expand or collapse filter card
• Remove (the filter card)
• Lock (the filter so it cannot be changed)
• Hide (nobody can see this filter card when it is published)

That is all we have for today.  Happy Filtering!

Screenshot includes the Filter panel this time for reference. 

Power BI Formatting Report Visuals - Snack 16

You probably noticed a few things about the visuals added that you probably do not care for.  Or at least, whomever you are building the report will not want to see.

The little table has values that go down to the penny, which is usually not needed for financials.  The "Sum of Profit" visual says "Sum of Profit," which is not particularly descriptive.

The visual that we added last time is titled, "Sum of Sales, Sum of Gross Sales and Sum of Profit by Year and Quarter."  While that is relatively descriptive, it is not exactly what we want there.  Also, the titles on the X/Y axis seem a little redundant, displaying information that is already highly visible.

This is where formatting the visual comes in and can be very useful in making things look just the way our audience would rather it appear.

Let's slip into EDIT mode on your report and make a few changes.

There are few things to know first that will really help.

After selecting a visualization, when you choose "Format your visual" under Visualizations, there are two tabs under that, "Visual" and "General."

• Visual Tab is for adjusting just about anything related to the presentation of the graphics and the data displayed inside visualization.
• General Tab is for adjusting the overall appearance of the visual, such as its size, shape, color, and other effects.

Also notice that there is a Search box at the top of those two points. There are so many adjustments that can be made to visuals that it can be a little difficult to find the right setting sometimes.  Look for them using this search box.

I am not going to make you do that today.  For now ... 

• Click the Table visual to select it for modification
• Under the Visualizations panel, 
• Choose "Format your visual" (NOTE:  To adjust the decimal, we can go two directions
• Visual Tab (adjusts the decimal places for this specific column in this visualization)
• Choose "Specific Column"
• From the drop-down under "Apply settings to"
• Choose Series "Sum of Gross Sales"
• Expand Values
• Change "Value decimal places" to "0"
• General Tab (adjusts the decimal places for this data point anywhere it appears in this visualization)
• Data Format
• Under "Apply settings to"
• Choose "Sum of Sales"
• Expand Format Options
• Format = "Whole number"
• Turn ON the "Thousands separator"
• NOTE:  You might need to expand your visual now and/or expand the column size, lest the wrap themselves.  We already covered how to do that.
• Click the Card visual to select it for modification
• Double-click the column name under "Fields" 
• Rename to "Profit"
• Click on that Bar Graph visual to select it for modification
• Under the Visualization panel, 
• Choose "Format your visual"
• Visual Tab
• Expand X-Axis
• Turn "Title" off
• Expand Y-Axis
• Turn "Title" off
• General Tab
• Expand "Title"
• in the "Text" box, type "Gross Sales vs Sales vs Profit"
• FILE
• SAVE

Again, there are a multitude of settings under "Format your visual."

There is a safety net for each of these settings too.  

After making an adjustment on any particular setting, "Reset to default" will light up and that setting can be restored to its original state.  Try it out.  You can always exit and come back without saving.

That is all for this round!  See you next time.

Wandering about the Yellow Stone

Yellowstone turned out much less annoying that initial perceptions suggested it might be.  The crowds promised were nowhere to be found. Weather proved as tolerable as the size of size of the civilization.  Temperatures generally started off around 5° C, reaching as high as 25 °C on one date.  All of this made for a relaxed and comfortable journey throughout the park.  The only looming opposition to planned progress turned out to be the pending government shutdown.  In the end, it too turned out trivial and easily managed. 

Our first day started with a hike from the front door over to the Grand View of the Grand Canyon of the Yellowstone.  It was the perfect start to a near perfect day.  Jumping on the road, partly cloudy skies led the way to a visit with Old Faithful.  Arriving just in time to find a front-row seat for the eruption with only a few minutes to spare, we did not linger for long.  We were concerned there would not be time for other points of interest.  It was good thing we did too.  The Grand Prismatic Overlook, Firehole Canyon, and a wrong turn along the trail in Norris Geyser Basin consumed the remainder of the day. 

The following day proved much more of a challenge as regards finding sunlit locations. Mostly cloudy skies and slightly cooler temperatures shrouded a good portion of the day. Escaping through Dunraven Pass, we stumbled on a wolf sighting just before The Petrified Tree.  The latter proved more of a sighting than the former.  A random detour easterly along the Lamar River turned up little beyond reconstruction of the overpass and a distant herd of bison.  Most "herds" counted as 10 or so loitering about the landscape.

Turning about halfway along, we made Mammoth Springs a proper priority and spent a good amount time lingering there before moving on to Gardiner and sustenance.  That is where we found the Elk were hiding, counting more than in all of the park. A lazy reentry through the Roosevelt Arch followed, managed by a dump truck ambling along at 25 km/hr.  It finally peeled away, enabling a more expedient progression on through the Silver Gate, ending with the amazing Sheepeater Cliff, before returning to the comfort of our quiet cabin in the pines.

The last day in the confines of the park was also the first day of the government shutdown.  Generally, it turned out to be a non-event.  Someone had told us they would boot us out.  That turned out to not be the case.  We had scheduled the visit to West Yellowstone for this day anyway, just in case, and proceeded with those plans.  Skies full of sunshine, coupled with billowing clouds in the distance and warmer temperatures, encouraged taking time to get there.  

Arriving in West Yellowstone, we found breakfast no less costly than it had been in the park, and relatively comparable in taste too, or lack thereof.  The Grizzly Wolf & Discovery Center offered an hour or so of amusement beyond that, though not quite on the scale anticipated.  It is a miniature zoo, with animals in jail.  The bears were not doing any product testing but were allowed to wander about The Yard for a short time only.  The wolves looked homesick and bored.  Heading back into the park, we headed over to the Grand Prismatic Spring in an effort to take a close look.  It had been clogged with traffic the day before and wholly inaccessible.  They have a very tiny parking lot that is a traffic bottleneck on the best of days.  

We found success in accessing the Grand Prismatic Spring on the return journey.  The day before the route into the parking lot had been clogged.  The size of said space contributes to the gridlock found there.  In celebration, we found our way back to the Grand Canyon of the Yellowstone for a look at the same from the South Rim.  That is not the place to be for sunset though.  The angle is all wrong, so Inspiration Point ended the day.  It is not a great place either but was a lot closer to our final food source of the day.

. . .

More video from these 3 days is available through a playlist on YouTube.

also

Stills from this adventure continue to be published to a gallery on 500px.

This post will be updated when that process is finally complete.  

lastly

A Google Maps view of the 3-Day Adventure for your enjoyment.

Tao Verse 10

Hold fast to the Power of the One

Entering the Caldera

A quick exit, a left turn at the oil derrick and a mile or so down the road led us to G-Ma's diner relatively early in the morning.  The AirBnB did not serve breakfast, which continues to be a point of discontent.  How can one advertise as a "Bed and Breakfast," if there is no breakfast?  Regardless, G-Ma's fit the bill perfectly, serving up what was probably the finest ever consumed west of the Missouri River.  Their sausage gravy captured the imagination, reflecting back to the appropriate level of spice by which Grandma peppered her sauce.

The road west unfurled quickly after that, under partly cloudy skies and temperatures that would likely spike into the lower 30's Celsius.  It would not end that way for us.  Steadily increasing altitude would move the needle in the opposite direction but only down to around 20.

The landscape remained largely unchanged from the eastern half of the state for a good portion of the journey.  Language proved quite the contrary as we discovered multiple in use at any given moment at the Exxon City Square of Shoshoni.  The disorderly conduct demonstrated an intolerable level of confusion, and we disembarked as quickly as possible, quickly crossing into the relative safety of territory controlled by the Shoshone and Arapahoe. Deep scars on the land emerged from the constant raids on their land from the meltwaters of our general destination, the adjacent Teton range of mountains.

Prior to assaulting this formidable opponent, we paused for a bit of refreshment.  Breakfast had been entirely too good and inexpensive.  We needed to moderate that with a poorly done and overpriced meal in Dubois so as to ensure that our expectations did not remain high for the remainder of the journey. The Cowboy Cafe fit the bill, though it felt as though just about any establishment in town might have done equally well.  As the last outpost on the trail to the Tetons, this bustling metropolis appeared poised ready to lighten as many tourists of their burdensome cash as possible; particularly, as it related to petroleum products.

Some of the most breathtaking scenery known to man rise along both sides of the roadway beyond Dubois. The Pinnacle Buttes, North Breccia Cliffs, Lava Mountain, and Angle Mountain fully engage the mind, drawing the motorist deeper towards the cruel and jagged edges of the Tetons.  There is no harm here though, only nature and its unrelenting, though failed, attempts to lift itself up to the sky.

We were relieved to finally be standing at the base of the Grand Tetons, across Colter Bay.  Many others were equally gratified at the scene, which included facilities that had been nonexistent in the past 100 km. After an hour or so waiting for the Grand Tetons to do something more spectacular than just stand there, we gave up and began to purse the path named after the infamous philanthropist, John D. Rockefeller to complete our quest to reach The Yellow Stone.

map image borrowed from National Geographic

Entering the caldera of this massive super volcano, multiple stops interfered with progress more than any of the wildlife.  We fully expected it to be the other way around.  The lateness of the hour limited our hesitations to three or four or five.  The beauty of the terrain was positively addictive when it broke from between the pines, and almost too much to leave behind.  

We were not required to leave it all behind after all.  Indeed, it was right off the front porch of our final resting place, nestled against the pines.  

We quickly discovered that Xanterra had attempted to exact some revenge upon us for not booking directly through them by placing us right next to a rather boisterous ice maker.  Their attempt failed though, as the noise could not get past the barricade to our chambers.  It did make lingering out of doors out of the question, excepting when the beast slept.  

The only thing left to do for the day was to wander over to the Canyon Village space for a meal that turned out as mediocre and overpriced as previously enjoyed outside the park.  There were three hopelessly generic choices overall, sealing our dining fate for the next several days.

The journey for the day had not been long.  Planning had anticipated actual road of around 6 hours.  Forward progress had lasted nearly 10 hours.  That was not much different than the previous day, or really any other exploratory adventure in the past.  We crashed hard and slept well, after making a choice between one bed with clean sheets and another deeply scarred with road dirt from being dragged in a bag across the parking lot (a practice noted in passing related to another resident as we arrived earlier).  If the volcano were to go this night, not a soul would care.

. . .

Further Reading

AirBNB

G-Ma's Diner

Casper, WY

Exxon Fast Lane Inc

Shoshoni, WY

The Cowboy Cafe

Dubois, WY

Grand Teton National Park

National Park Service

Yellowstone Caldera Map

National Geographic Education

Canyon Lodge & Cabins

by Xanterra

Canyon Lodge Eatery

also by Xanterra

Yellowstone National Park

National Park Service

Kong to Casper

An unexpected surprised greeted us in the morning at hotel the next morning.  The supplied breakfast consisted of much more than the heartbreaking "continental" variety.  A wealth of eggs, sausage, biscuits and gravy, and waffles joined in the fray and in no time, with a fully belly, we bid farewell to our neighbor King Kong.

Rocketing down the ramp at Little Salt Creek, the interstate rolled out across the Platte River valley under blue skies and temperatures that would reach in the low 30s Celsius.  In as much time as it took to take a breath, it was clogged with high-speed traffic.  Everyone seemingly raced for the same destination, hoping to arrive first.  Construction threatened to foul the pace for all involved.  It certainly made for a more intense experience, if nothing else.  Side-by-side with unrelenting truck traffic at 120 kph can be quite daunting. Collapsing all of that into one lane, between concrete barriers can be disheartening.  One forgets about everything beyond self-preservation.

Ogallala offered two lanes and substantial relief, aiming straight for Lake McConaughy, as if this were the path of choice with many before.  It enabled a more windows down and music environment, skirting along the southern-most extent of the Nebraska Sand Hills without missing a beat The oppression and monotony of the interstate quickly faded from memory.

A miscalculation caused us to miss a return visit to Scottsbluff. Plans to take a quick tour to the top and break there for lunch went down in a fiery crash when a missed left turn found us on the north side of town.  While the view felt every bit worth taking the time to diverge, something told us to press on.  

Wyoming arrived shortly thereafter, unassuming and unannounced.  We hardly realized its presence until we were already well into the argument.  Fort Laramie, and our proximity to having join with the interstate again, turned the conversation towards taking a break.  It went flying by, though our maps indicated were barreling fast towards other points of interest noted during the planning process.  

Over last two hundred years or so, everyone passing through took some time to register themselves upon the nearby soft and chalky limestone formations that rise above the North Platte River Valley.  We arrived at Register Cliff State Historic Site to ensure we were properly registered but found no place left to make our mark.  Prior trappers and pioneers had consumed nearly every square inch of space available, leaving only enough room for folks through to the 1980's.  There had been a lot of traffic.

Most of that traffic occurred between 1841 and 1869, as the masses migrated along the Oregon Trail, searching for that promised land in America.  Their wagons etched an impression on the surrounding limestone cutting deeper than any of their signatures.

It is a quiet place now, atop a hill overlooking the North Platte River.  It seems odd finding these old wagon tracks up high, when a wide and flat river valley lie just below.  They were either missing the mark completely, avoiding something down there, or the landscape must have been decidedly different.

Lucindy Rollins might have been able to explain the situation, except that she passed on earlier than anticipated on these bluffs.  Her name is forever immortalized upon an obelisk dedicated to her memory here.   She never made it her appointment or to whatever it was that coerced her into making the long trek across the forbidding outback of the United States at that time.  

Intent on making our appointment to arrive in Casper prior to dusk, we turned our full attention to that task. I-25 assisted in that process, as well as a speed limit suitable to crossing distances in a short amount of time.  They should post these signs everywhere!  

We were successful in our goal.  Our camp host greeted us under a quickly darkening sky, and by the time we arrived at the recommended J's Pub & Grill, darkness had consumed what was left of it.  We survived a mediocre meal there and returned to collapse into bed without further incident.  Tomorrow would be a bit less harried, undoubtedly.

Sunset and Cookies

"The best laid plans of mice and men often go awry," and the start of the expedition out to the Yellow Stone proved no exception.  Plans to arrive in Lincoln for sunset were quickly derailed by a need for sustenance.  We could have forged ahead but maintaining amicable relations among our crew felt more important.

Approaching Tracy, a prior recommendation came to mind, and the Country Cafe happily supported the previously mentioned effort with a quick and delicious meal that surpassed expectations.  Fat and happy, nobody cared that the last 100 or so kilometers of the day would be under cover of darkness.

A pause for fuel on a bluff near Rock Port in Missouri enabled an opportunity to momentarily admire the ongoing descent of the great ball of fire in the sky.  The situation left little time for any other diversions, though that was understood to be a facet of this leg of the journey.  Indeed, we hardly even noticed passing through Iowa.

Lincoln appeared shortly after without incident, and any concerns related to our first host quickly evaporated.  Bridgepoint Inn & Suites offered a friendly, clean and well-kept establishment.  While nothing fancy, those three points go a long way towards supporting the proper state of mind on any expedition.   

Dusting ourselves off and settling in, a celebratory walk across the street enabled acquisition of a dose of hot chocolate and cookies.  That put the day in perspective, readying the soul for the coming stretch across Nebraska and Wyoming in our quest to reach Yellowstone.

Power BI Report Chart Visual - Snack 15

That last visual proved interesting.  Let's add something a bit more interactive.

Jump into EDIT mode on your report.

Ensure you do not have anything select by clicking in the blank space.

A quick double-check is to look at the "Build Visual" section of the Visualizations pane.  "Values" should display nothing more than "Add data fields here."

Let's do that.

1. Drag-and-Drop the date field to that little box.
1. A new visual should appear with the date
2. It is broken out by Year, Month, Quarter, Day.
1. That is good.
2. Do the same and Add Sales.
1. You should be able to Drag-and-Drop it just below the Date portion.
3. Add Gross Sales.
4. Add Profit.
1. We are back to a boring table.
5. Under the Visualizations pane, 
1. click on the icon for "Line and clustered column chart."
1. The boring table should change to a fancy bar chart.
1. If it does, great!
2. If it does not, and only a new blank visualization appears, that means your table was not selected when you clicked that icon.  Select your table visual and try again.
1. You can also select the other blank visual, hit that little ". . ." menu and choose to remove it.
6. Stretch out your new visual so you can see more of it.
1. It is probably only displaying 2 years columns, and how come there is a line on the icon and none on the chart?
7. Return to the Visualizations pane,
1. click and drag "Sum of Profit" down to where it says, "Add data fields here" in the section beneath that for "Line-y axis."
1. Ta-Da!  We have a line!
2. Still a little boring though.
8. Hover over the visual.
1. Click the icon that looks like two down arrows that split into two.
9. Save.

That's better.  Your screen should look relatively similar to the screenshot.

Make absolutely sure you saved before reading on.

Did you SAVE?

Are you sure?

You should probably exit and come back into the report to ensure you do not muss anything.

There is no exit.  I am not sure why.  Close the tab.

Access the report again.

Now, about the arrows ... 

The arrows enable you to interact with the visual, as well as set the default appearance.

When I first started helping folks out with using Power BI, I made a little cheat sheet for these little icons that shows up below.

Play around with the arrows to better understand how they work.

Planning for the Yellow Stone - Phase 5

Just when you thought there was nothing left to plan for, guess again.

Things need piled up.  Plans need distributed to those with concern. New hiking shoes need broken in. 

It would be a little ridiculous to get on the trail with a stiff pair of shoes.  

I cannot think of a better way to get blisters.  

Beating up the new foot cushions on local trails are good insurance against that, especially if they are an entirely new model, with different traction and balance considerations dynamics.

There is a distinct difference the new set and my old pair of Columbia Redmond III.  Discovering that they were no longer made proved extremely disappointing.  I have been using that model for years.  They are heavier than most, with a much thicker tread that just seems to grip better.

Replacement tires are Merrell Accentor 3.  They are definitely lighter, though lack the same grip, leaving me stumbling about a little more than anticipated.  There will definitely be a period of adaptation.

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.

Tao Verse 9

 

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

Power BI Report and Semantic Model Naming - Snack 11

While thinking about what would be next for this series, I went to access the report we have been working on and found the proper topic before even opening the report.

If you have been following along and using the same sample dataset, you might have noticed a small inconsistency in naming between the Report and the Semantic Model.  This is captured in the screen shot above.  

Yours may or may not have this issue.  If it doesn't, aren't you lucky!?

I am not entirely certain what happened here, and it is mostly irrelevant.  

Usually, whenever you create/publish to the Power BI service, the name used for the report will be exactly the same as the Semantic Model.  For whatever reason, it did not this time.

This kind of bothers me.  I prefer that both names match so that they remained paired up and intend to rename both of them to "Financial Sample."

This can be done within the service and not impact anything.  They will remain connected.

How do we do that?

Access "My workspace," and ...

For the Report,

1. Hover over the row with the report name
1. A ". . ." menu appears next to the report name.
2. Click the " . . . " menu
3. Choose settings
1. Here you can choose to
1. Name / Rename the Report
2. Give the Report a Description
3. Add Contact Information
4. Choose an Endorsement for the report
5. Multiple other useful settings
4. Rename the Report
5. Save

And for the Semantic Model,

1. Hover over the name of the Semantic Model
1. A ". . ." menu appears next to the report name.
2. Click the " . . . " menu
3. Choose "Rename"
4. Rename your Dataset name aka Semantic Model.
5. Save

That is really all there is to it.  To me, everything looks much cleaner now and my mind immediately recognizes that these two are connected.  I do not have to waste any though on that.  Hopefully, it helps you too.

Planning for the Yellow Stone - Phase 2

The next phase in planning for the journey across the western plains involved identifying exactly where we were going to stay, as well as  what might be worth investigating along the way.

Most of where to stay had been decided in the previous, based on availability of lodging and reviews of available lodging.  It was time to narrow things though.

Indeed, the stay in Lincoln had been driven by the alternatives.  Anywhere else within 3-5 hours of home along our route proved wholly unacceptable.  The reviews of establishments along I-80 ranged from horrible to down right frightening, leaving one to wonder if the area were being terrorized by desperados.  It was Lincoln who seemed the only safe haven.  We will confirm that in the post mortem.

The interstate hotel terrorists final destination might as well be Casper.  It seemed to host the same issue with much of the establishments.  We were lucky enough to find a basement apartment Air BnB.  A little more luck and we will survive the night long enough to make a break for Yellowstone the following morning.

image borrowed from YellowstoneParkLodges

The middle of Yellowstone seemed to be the best choice all around for those arrangements.  Rates at Canyon Lodge & Cabins seemed reasonable enough for base camp, and we could not think of a better way to become acquainted with the park, especially if we end up snow bound.

Locating a place more specific than the general locale turned out a much easier exercise for the journey homeward.  Our estimates indicated that adequate overnight facilities were very likely facilitated by a lack of proximity to any highly traveled roadway, in particular the interstate system.

In the midst of all of this decision making was going on, I also began scanning the route for various points of interest.  Actually, this had been happening all along, I just focused on it a bit more.

All of the routing had already been plugged into MyMaps.Google.com.  Adding sights to see was a natural evolution for this situation. 

Locating interesting objects along the way is more awkward and time-consuming than it should be these days. I am not sure who to hate for that, excepting maybe mobile device design trends and an inability of software makers to fully understand user needs.

image borrowed from NewEgg

The best trip planning software Microsoft could produce still trumps anything available now.  "Streets and Trips" could do all of this in a flash.  One could plan a route more easily, get relatively accurate estimates of travel time and costs, customizable based on your specifications, as well as easily identify any sort of category within a certain distance of the route.   For the life of me, I will never understand why such useful software is abandoned.  Nothing even comes close these days.

Lacking that most useful tool, the only option is manual review, which consists of zooming down to whatever scale enables a view of about a couple of miles or so of the route, then simply scanning the route for anything nearby. It does give one a sense of the proximity of more serious points of interest, like somewhere to eat.  That is not something that can really be planned.  It is nice to know available options at end points though.

The result of all of this maneuvering appears in the map below.  When the post mortem is conducted, the actual detailed maps will be shared along.

The only thing to plan now is the visit inside Yellowstone itself.  Given the number of layers already present in the little mapping application, a special map will need dedicated to the next phase.