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!

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.