Afternoon Escape to the Lake with Catfish

 

Skipping out of town on a random Wednesday afternoon, I headed south into the Lake of the Ozarks.  The mission for the the day was to meet with an old friend I had not seen or heard from in a very long time.  

The journey took about as much time as expected, with the winds and temperatures of a looming cold front chasing me the entire way.  The cloudy skies did not follow along at all, thankfully.  Clear blue skies dominated the afternoon in this neck of the woods.  "Chances R" hosted an excellent lunch for us, where the Catfish were extremely well fed and very delicious.  

While the time and the food were more than we really wanted to leave, rampaging deer populations intent on impaling themselves upon any vehicle available generally encourage departure in sufficient time to ensure a keen ability to spot them before the opportunity arrives.  

I had intended to simply return the way I had come.  It had been pleasant enough rolling down MO-135 but I missed my turn, semi-intentionally.  Heading north through Gravois Mills, I found a once booming economic hub now a shadow of its former self, though still offering the perfect sort of escape on the edge of the Ozark insanity.

The remainder of the journey proved mostly unremarkable.  A pause in Sedalia offered up an interesting older piece of art on the side of a building.  Beyond that, I found little else worth a pause, only a detour.  Someone had fouled up the flow of things along US-50 right at MO-13, forcing a detour to the north.  This forced an encounter with I-70 and major construction, somewhat ruining what was other was a great out on the road.  

Tao Verse 12

the Sage is led by his inner truth and not his outer eye

Power BI Report Mobile Layout - Snack 20

In this mobile world, you might want to enable a cleaner view of reporting for those interested in accessing reports through the Power BI app.

You didn't know there is an App for that?

There is indeed.  Look it up in your favorite app store and access all of your reports on the go, after we get this ready, of course.

Let's get going ... 

1)  Jump into your report and switch to EDIT mode.

2)  Click "Mobile Layout," at the top of the screen, just underneath the title of the report.

-   A screen appears offering the opportunity to "Create a mobile layout."

-  Do NOT click that button that says "Auto-create mobile layout."

-  Try that some other time.

On the right of the screen are two panels, "Page Visuals" and "Visualizations."

The "Page Visuals" panel displays all the elements from the report created.

The "Visualizations" panel enables you to modify the element for better visibility in mobile view, without impacting the original desktop display you created.  To do so, click the element you want to adjust, and the panel will populate with options available for modification.

3) From the "Page Visuals" panel, click, drag & drop the little boxes out onto the white area that looks like mobile device.

-  Take them one at a time.  When they appear on the mobile device layout, gray handles appear enabling you to resize and reposition the element. Position and size it, then move on to the next.

-  It will not let you stretch off screen, thankfully.  

-  It will let you lay them over the top of each other though, so beware of that.

-  Hover over and scroll on the mobile device layout to get more real estate.  

4)  File > Save

-  To get back to the original Desktop Layout View, click the icon in the lower left of the screen that looks like a computer screen.  

 

. . .

I hope you enjoyed tagging along with this little exercise.   This concludes the last regular installment of these little snacks.  There will be other editions, just not quite as frequent, I think.  You never know.

There is a lot more that can be done in Power BI.  We hit all the core elements though.

Everything else can be figured out by clicking around, asking around the interwebs, or your favorite Artificial Idiot (AI).  The latter is usually right, most of the time.  You know how it is.  You are doing 100% better if you figure it on your own though.

Microsoft hosts an excellent support community (Power BI forums - Microsoft Fabric Community).  The folks over there can answer just about any question or at least work through figuring it out with you.

Additionally, Guy in a Cube on YouTube is an excellent resource for tips, tricks, demos, feature reviews and really digging into the guts of Power BI, and they are more interesting than most.

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!