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.

Two Black Hills and a Rattlesnake Sunset

There was really no way of avoiding the interstate today. We were definitely looking forward to getting off that usually wretched confluence of speed, population, and careless drivers as quickly as possible.  Ultimately, it turned out to be not quite as bad as we thought it was going to be, with expedience the only concern, and that being more of a blessing than anything else. 

The Bighorn Mountains quickly faded into the distance as we crossed the Powder River Basin at high speed, quickly arriving at the exit we still longed for that would return us to the peaceful pace of two-lane highways.  The "Home of the Bear" appeared not long after that, standing tall against the horizon with its less recognized sisters, the Missouri Buttes.

A crimson landscape surrounded the volcanic plug known to most these days as the "Devil's Tower," and made more infamous by Spielberg movie "Close Encounters of the Third Kind.  Standing at the base of this laccolith kind of ruins some of the intrigue of that performance though, bringing a lot of actions into question.  The journey around the base is quite interesting though, offering views not usually seen and vistas stretching in all directions.  It did not seem to matter that the government was shut down, for us or anyone else. There were plenty of anyone else too, and overflowing trash containers, and this only 2 days into the problem.

The Beautiful Fork called to us from these Black Hills of Wyoming.  My comrade had demanded the diversion to the current point of interest.  I thought that since we were in the area, we might as well make a stand at the center of the known United States universe too.  Quite a bit more than just a marker greeted us at this location.  That was all that had been expected.  An interesting museum, hosting a variety of artifacts stood by its side to amuse and entertain guests on the history of the immediate vicinity.

Departure instigated a discussion ensued regarding the route to our destination for the evening.  One side of the argument extended the journey by an hour so, through a location from a previous visit.  Roughlock Falls would have to wait for another time though. The sun was beginning to fall out of the sky more quickly than originally noted.

As reward for passing through the regime of the Pactola Reservoir, we were treated to a bit of off-road adventuring.   The state had quite a little length of US-385 ripped entirely from its bed. It proved too much for the GoPro, whose battery life threw in the towel shortly after.

The decision to push and avoid the Roughlock diversion proved a wise choice after all.  We arrived at Ben's Cabins in Custer just as the sun began its descent below the horizon.  It offered occasion to unload and decide upon the next intended target that would enable sustenance enough to carry us into the next day.

The Mount Rushmore Brewing Company was found to be the most endowed to enable completion of the quest.  Watching the sun complete its descent from their back deck, a sample of their local distinctions, followed by some deliciously prepared salmon and rattlesnake topped off a nearly perfect day.

Crossing the Bighorn

Another beautiful day unfolded in the caldron, under mostly clear blue skies.  We would only be permitted to enjoy part of it though, but that did not keep us from dragging our heels just a little.  We paused again to look out across the golden expanse of the Hayden Valley before pushing on to views previously unseen.

The small expanse of the park beyond the Fishing Bridge enabled a few moments of quiet beside the lake, a pause to gawk at another grizzly that really did not want to be bothered, and a few more quiet moments beside Sylvan Lake before winding our way through Sylvan Pass and out of the park.

If it had not been for the gates at the entrance, we would likely have never known we had left until we reached Cody.  The scenery was equally as dramatic tracing along the Buffalo Bill Scenic Byway, which did its best to follow the North Fork of the Shoshone River.  As many pullouts along the way offered the chance to view fantastic formations such as those near Clocktower Creek, a random Shoney's boy, and the Smith Mansion.

Pre-Cambrian Granite, some of the oldest in the world, became much more prominent beyond that.  It is emphasized in the landscape surrounding the Buffalo Bill Reservoir.  It was mostly still and clear on this date, though still held fast by what was once the highest dam in the world.  Our desire to pause and examine this masterpiece of innovation proved thwarted by ongoing renovations though.  We had to find satisfaction in simply passing through the surrounding rock face.

Emerging on the other side, we immediately encountered the Rodeo Capital of the World, though it was closed for the season; the rodeo, that is, and not Cody.  The town itself offered an opportunity to enjoy a taste of decent coffee, as well as other items of importance further down the road at the visitor center.  It facilitated needed relief and presented a view into the arts community of the area. 

Endless two-lane highway stretched out across as far as the eye could see and proved to be the perfect level of solitude in almost every way.  Nicely finished blacktop surrounded by scrub and occasional cropland evoked the feeling that we might just have entered the Twilight Zone.

Near Greybull and the beginning of the southward movement we encountered an interesting feature in the lowly rest area. A lot of thought is put into this perfect setting for geodesign; a south-facing orientation, the right kind of thermal mass, insulation and circulation, and of course, lighting.  Get it wrong and it is a worthless seasonal structure.   Get it right and afford travelers the perfect oasis in the depths of a Wyoming winter.

Just behind the Passive Solar Rest Area, the Museum of Flight & Aerial Firefighting waits to amuse and entertain those recently afforded relief.  This would definitely be interesting affair, if it had not just closed for the season the day before.  The most enjoyment it offered were a few snapshots from behind the fence.  

The second half of the journey across the Bighorn Basin found the peaks of mountains of the same name quickly rising up from the plain.  Fields of gold skirted all around this range, with wide open blue skies above, encouraged temperatures to remain solidly comfortable near 25 °C.  Approaching Ten Sleep, a glimpse of an intriguing formation flashed by to the south, disappearing into the horizon just as quickly as it had appeared.  

Approaching Ten Sleep, a glimpse of an intriguing formation flashed by to the south, disappearing into the horizon just as quickly as it had appeared.  While considering an about face and retreat to have another look, a sign appeared, encouraging departure from our intended path.  About 6 km later, we stood in the middle of nowhere, gazing up at the formations of Castle Gardens Scenic Area.  It did not take any encouragement to go creeping about the complex, climbing to the top for a look around, while keeping an eye for wiggly bits with rattles.

Satisfied and back on the road, we took the Bighorn Mountains by storm.  One of the more intriguing parts of these mountains is that the state of Wyoming has taken the time to go through and explain the various geological aspects at play here.  Signs along the way identified formations and their relative age, making for not only an awe-inspiring journey, but one educationally oriented, as well.

We landed in Buffalo, after sliding down the eastern slope of the range, in short order.   The quaint little roadside motel on order ended up every bit as expected, and even included its own little park area, complete with picnicking facilities and implements.

We were unprepared for all of that though, and taking a raincheck on the playground equipment, unloaded our gear and headed deeper into town to discover sustenance.  To our surprise and delight, we discovered so much more than that.

The evening ended much later than we had initially anticipated, but not too much so.  We had a relatively good meal, served slower than a 7-year itch, while enjoying some incredible music from an army of talented musicians.  It just doesn't get any better.

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 500px and a link to a more specific gallery will posted here upon completion..

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