Learning from the Microsoft Power BI Sales & Returns Sample Report

Over Xmas and the New Year break, I came across this blog post from Tessa Hurr, Microsoft Program Manager:

https://powerbi.microsoft.com/en-us/blog/take_a_tour_of_the_new_sales_returns_sample_report/

I was really super intrigued by this excellent post as it showcased some of the more recent developments within Power BI.

If you have not read it, I strongly urge you to read it now and then please do come back to me!

OK – you’re back! Great!

Find your downloaded copy of the Power BI sample report or just download it again from here: https://go.microsoft.com/fwlink/?linkid=2113239.

I will now walk through the Sales & Returns sample pbix file and highlight what I learned from it! I made the sample file read-only so that I don’t inadvertently change it.

Here’s the first screen we will all see when opening the sample file:

Here are my observations from the Legal page and initial thoughts.

  1. Legal page layout has been set in portrait mode which is good for both mobile phones and desktop views.
  2. A static Microsoft logo image has been added using the standard Insert Image button.
  3. A Text Box has been added with the very important legal disclaimers with links to more legal agreements.
  4. The whole report uses only four custom visuals with two of those (Simple Image and Image) being for specialised Image visualizations. I am at this stage wondering why two custom visualizations for images?
  5. There is a total of 18 pages in the report but only the first five are visible to report viewers.
  6. If you expand the Filters pane then you will see that there is a “Date – Month” filter that has been set to only show the month of June.
  7. If you look in the Fields pane, then you will see Analysis DAX and Design DAX. This is great as all the DAX measures are in one place and they are also split across function. I really like this, and I will create my next Power BI Desktop report using this same approach!

Now looking at the Intro page, this is what we see:

It’s got a standard text box containing a short description about this sample report, also a nice button to download the actual PBIX if you are viewing using the Power BI service. The web url the button is configured to point to is: https://go.microsoft.com/fwlink/?linkid=2113239.

If you look at the vertical line with 3 circles on it, you will notice that the report opens up on the “Design Factors” screen. This navigation bar actually calls 3 bookmarks which explain the 3 key areas of Design Factors, Visualizations & Elements, and Questions & Experience.

The design factors screen shows how the report authors have used the matrix visual as a table of contents. Great innovative use of the simple matrix visual using the row-headers, stepped layout feature!

Really nice background image named “BACKGROUND-01.png”; Let’s see what other images are used.

Use your favourite unzip program and extract the contents of the pbix file to a new directory. Navigate to where you extracted the file. In my case it was here:

C:\Users\Sunny\Downloads\SR-pbi-extract\Report\StaticResources\RegisteredResources

You should see something like this:

When you read the original Power BI blogpost you will have seen a reference to Chris Hamill and his blog site at https://alluringbi.com/gallery/. The great news is that the main backgrounds are available here in PowerPoint format so now we can use them to help make our own reports just as impressive!

Chris also has a step-by-step process detailed here: https://alluringbi.com/2019/10/21/background-concepts-for-power-bi/ which I highly recommend you reading.

Back in the sample report, if you now ctrl-click the second circle down, you should see this: (oh yes; I turned on the Bookmarks pane, so it was easier for to do a screen grab!)

Did you see the animated tooltip? Wow! My clients are going to love this! I have very fond memories of animated gifs – CompuServe tried to monetise it, but forgot they used LZW compression and that was patented by, my employer at the time, Unisys! Anyway, that is another story for another day…

So, this must be where the Image custom visual comes in. It must be needed so as to display animated gifs properly? The report configures the Image to be in a circle, but if you want to see the animation full screen in your web browser, then it is here: https://imagizer.imageshack.com/img923/4052/lLHy3U.gif

The third circle down highlights the Info Tooltip functionality which helps explain to people what is going on behind the scenes.

OK – let’s move onto the next page which is the Net Sales page. You will see this:

The first thing I noticed in this well-laid out report was that the last refresh date was up in the right-hand corner.

So, let’s refresh the report and see it update! You will see this – Boooo! We don’t have the Dropbox Spreadsheet file!

Of course, the Last Refresh is not going to change in the report. Let’s look at how they are doing it by looking at the text box. OK – This was a little disappointing to me as it is just a static text box with the title text set as “Last Refresh: Jun 30th, 2019 / Chicago, IL, USA”.

I would have thought the authors could have showcased getting the last refresh date using M.

M-aybe a bit like this:

This gets you:

Also, we can get your current location using the Power of the Internet Elders and some more M-agic:

This gets you:

And now you know where I live! Or do you?!

I didn’t implement this into the sample report as it seemed important for the authors to state Chicago, IL, USA – I just wanted to give you some food for thought for your own future reports!

The next thing I noticed was that the report was using the Decomposition Tree visual. As that is a preview feature and I forgot to turn it on, I will need to do that now. Go to File, Options & Settings, and then Options to turn it on. We might as well turn on the new ribbon as we are here! You will see this:

Restart Power BI and open the sample report again, go back to Net Sales tab. You should see something like this:

Ctrl-Click the Decomposition Tree and play around with it to see it work in action. This is a great tool to let your colleagues find insights for themselves in an engaging way. Here’s an example I created:

Opening the Returns tab, and playing around with the Decomposition Tree gives me this:

This is just a great new way to explore the data.

Moving onto the Return Rate tab, this is what you will see:

As we found out the OneNote skateboard had a high return rate of 24%. This report demonstrates the What-If capabilities of Power BI and I have moved the slider to 8% to see what the profits would be if we were able to reduce the rate of returns by two-thirds. This gives us a whopping 21% increase in profits!

The matrix and line charts are interesting as they use Analysis DAX to visualise their results. I was a little surprised that the Rules for DAX code formatting weren’t followed! Here’s a couple of What-If DAX formulas that I looked at.

WIF Profit – formatted by DAXFormatter.com for readability:

WIF Profit =
IF (
    [WIF Same] = 0,
    0,
    IF (
        ROUNDDOWN ( [WIF Adjusted Net Sales] – [Net Sales], 0 ) < 0,
        0,
        ROUNDDOWN ( [WIF Adjusted Net Sales] – [Net Sales], 0 )
    )
)

WIF Same – formatted by DAXFormatter.com for readability:

WIF Same =
IF (
    [% Return Rate Value]
        >= CALCULATE ( [Return Rate], ALL ( Store[Type] ), ALL ( Store[Store] ) ),
    0,
    1
)

The DAX code on the whole seemed straight forwards to me; however, this was a good use of the Switch function:

Product Top N =

SWITCH (

    TRUE (),

    ISINSCOPE (‘Product'[Product]),

    RANKX (ALL (‘Product'[Product]),[Net Sales],,DESC),

    ISINSCOPE (‘Product'[Segment]),

    RANKX (ALL (‘Product'[Segment]),[Net Sales],,DESC),

    RANKX (ALL (‘Product'[Category]),[Net Sales],,DESC)

)

If the above does not make sense to you or you just need a refresher, then go here: https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

The last visible report tab is for the Basket Analysis which looks like this:

The most interesting item here is the Open PowerApp button – when you Ctrl-Click it then it opens up this:

Again, super cool use of animated Gifs with a layered background to really show people how to do stuff.

I learnt (and re-learnt) a ton of stuff from this sample and hope you enjoyed learning along with me too.

See you next time.

Sunny

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: