PowerBI.tips

Category: Building Reports

The Dynamic Time Matrix
Time and time again when I begin talking with Excel users and ask to see what current reports they are using, they usually show me a table with a mixed bag of columns split by different time ranges. A comparison of month over month, or year over year, mixed in with a few daily totals or cumulative totals all rolled up from values on other tabs to produce their preferred view (or dashboard). Typically, the first approach I take is to describe how we can break up this single table view of things and start looking at the aggregations of these values in easily digestible pieces and slice and dice them in different times ranges. I’ve explained that the goal should be to produce easy to consume visuals for comparison using measures and plotting these out in different ways to glean insights quicker. Most of the time, this works, but other times… it is second best to what the analyst or uber Excel user wants to see… they want to see their numbers, and they want to see them the same way they have them in Excel.

The Challenge:

Recently, I encountered this all too familiar scenario (Time Ranges in a table/matrix) except this time, I wanted to see if I could reproduce the output exactly as the end user wanted it rather than move them in a different direction.

The first group of columns showed the days in the current week, the second group showed the weeks in the current month, followed by the Months to date, a year to date column and static columns of a Goal and Forecast.

I’ll spare you the details of researching a better way than producing these as individual measures, and suffice to say that I was able to come up with a solution based on a few calculated columns, a disassociated table, and a single measure to produce the output I was searching for.

The Solution:

The above screenshot is of the dynamic matrix that you can download from the link at the end of this blog. As I developed this solution it came to my attention that there are actually a couple ways we could build this solution. The first of those would be to have a time slicer drive all the different time ranges, this would be useful for analyzing older datasets in the different ranges, but my goal was to create a solution that follows the “Set it and forget it” train of thought. This solution will restrict the view of data to never exceed the current day, the neat thing is, the current day is when you read this blog, not a static point in time. I’ve pre-loaded data out to the end of 2020, so the sample should continue to work and change each time the file is opened.

Before we dig into things, I want to convey that the DAX dove a bit deeper into the weeds than I initially expected, and I’ll do my best to describe what I did and why.

The Data

Sample Data

I’ve modified my original solution to use a sample of Adventure works data that I created, this simple dataset consists of a column to group things by (ModelName), a date (StartDate) and the value to aggregate (ListPrice). This solution should cover a wide range of different use cases so don’t get hung up on the exact columns here. A grouping column, a date column and a value column are all you need.

Here are the steps I took after creating the dataset and loading it into Excel:

Load Data table from Excel into Power Query, Close & Apply

Create a calculated date table (DAX):
```
Date = 
VAR MinYear = 2018
VAR MaxYear = 2020
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ), 
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Calendar Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Week Number", WEEKNUM([Date]),
    "Weekday number", WEEKDAY( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)
```
Your MinYear / MaxYear will obviously be different, but the core columns for what we need are in this output.

Add Calculated Columns

Now we need to add some filter columns to the date table we just created in order to get the current time frames we care about.
```
IsInCurrentYear = if(YEAR(NOW())= [Calendar Year],1,0)
IsInCurrentMonth = if([isInCurrentYear] && MONTH(NOW())=[Month Number],1,0)
IsInCurrentWeek = if([isInCurrentYear] && WEEKNUM(NOW())=[Week Number],1,0)
```
Create a Disassociated table (Dax Table)

This is our grouping table, this is the first key element in which we create a series of different DAX calculated tables to create the different time range groups we want to roll up our aggregate amount by. In each case, we are pulling all current and previous years, the current months in this year to date, the current weeks in the current month and the current days in the week. Then we union those values together where the “Group” is the top level time range, and the value is the specific time range values. Then we add an index column so that we can order the values in the way that we want.

The final output should look something like this:

This is the DAX code to create the calculated table. Each “Summarize” creates the time groups and values rolled up for the particular time range we are interested in. This is wrapped in the “AddColumns” function to add in a workable index that allows us to order all the values in the correct order dynamically. Initially, the static Index column works to sort the Group column, but the dates won’t sort as Calendar dates so I added the second way to dynamically generate an index to sort the values by. I retain the original Index value and ensure the counts returned from the date table align in sequential order. Essentially retaining the Group/Value index to sort by. Then we wrap all that in “SelectColumns” so that we can specify the column names. If we didn’t do this, the first column name would be “Calendar Year”.
```
TimeRange = 
SELECTCOLUMNS(
UNION(
    ADDCOLUMNS(
    SUMMARIZE(FILTER('Date', 'Date'[Calendar Year] <= YEAR(NOW())), 'Date'[Calendar Year], "Group", "By Year", "Index", 4),
        "DayIndex", CONCATENATE(4, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Calendar Year]<=EARLIER('Date'[Calendar Year]))),"000"))
            ),
    ADDCOLUMNS(
               SUMMARIZE(FILTER('Date', 'Date'[Month Number] <= MONTH(NOW()) && 'Date'[Calendar Year] = YEAR(NOW())), 'Date'[Month Number], "Group", "By Month", "Index", 3),
        "DayIndex", CONCATENATE(3, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Month Number]<=EARLIER('Date'[Month Number]))),"000"))
            ),
    ADDCOLUMNS(
               SUMMARIZE(FILTER('Date', 'Date'[IsInCurrentMOnth] = 1 && 'Date'[Week Number] <= WEEKNUM(NOW()) && 'Date'[Calendar Year] = YEAR(NOW())), 'Date'[Week Number], "Group", "By Week", "Index", 2),
        "DayIndex", CONCATENATE(2, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Week Number]<=EARLIER('Date'[Week Number]))),"000"))
            ),
    ADDCOLUMNS(
        SUMMARIZE(FILTER('Date', 'Date'[IsInCurrentWeek] = 1 && 'Date'[Date] <= NOW()), 'Date'[Date], "Group", "By Day", "Index", 1),
        "DayIndex", CONCATENATE(1, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Date]<=EARLIER('Date'[Date]))),"000"))
            ),
               DATATABLE("Header", STRING, "Group", STRING, "Index", INTEGER, "DayIndex", INTEGER,
                   {{"Goal", "Overall", 5,0}, {"Forecast", "Overall", 5,0}})),
    "Value", 'Date'[Calendar Year], "Group", [Group], "Index", [Index], "DayIndex", [DayIndex]
)
```
Create a relationship between the Date table and the Data Table

This would be on ‘Date’[Date] and ‘Data’[StartDate]

Create our Measures

Now we need to take the grouping table and merge it with the aggregated value via our measures. In the Data table we want to create the following measures.
```
First Measure:
List Price = SUM(Data[ListPrice])

Second Measure:
TimeValue = 
    VAR Val =
    SWITCH(SELECTEDVALUE('TimeRange'[Group]),
        "By Year", CALCULATE(TOTALYTD([List Price], 'Date'[Date], FILTER('Date', 'Date'[Date]<= TODAY())), FILTER('Date', 'Date'[Calendar Year] = VALUE(MAX('TimeRange'[Value])))),
        "By Month", CALCULATE(TOTALMTD([List Price], 'Date'[Date], FILTER('Date', 'Date'[Date]<= TODAY())), FILTER('Date', 'Date'[Month Number] = VALUE(MAX('TimeRange'[Value])) && 'Date'[Calendar Year] = YEAR(NOW()))),
        "By Week", CALCULATE(SUM(Data[ListPrice]), FILTER('Date', 'Date'[Week Number] = VALUE(MAX('TimeRange'[Value])) && 'Date'[Calendar Year] = YEAR(NOW()) && 'Date'[Date]<= TODAY())),
        "By Day", CALCULATE(SUM(Data[ListPrice]), FILTER('Date', 'Date'[Date] = DATEVALUE(MAX('TimeRange'[Value])))),
        --Remove SWITCH below if you only want time range
        SWITCH(SELECTEDVALUE(TimeRange[Value]),
           "Goal", [List Price] * 1.2,
           "Forecast", [List Price] * RAND() 
            )
    )
    RETURN
        FORMAT(Val, "CURRENCY")
```
Create the Matrix

Create a Matrix visual and drop the columns into the following rows and columns:

You will have something that looks like this:

Dynamic Time Matrix First Level

Are you ready for the magic? Head over to the far right of the visual and click down on the “Expand all down one level in the Hierarchy” button -> and BOOM!

Dynamic Time Matrix Complete

We have our fully functional time range matrix that will adjust dynamically based on the current day. No need to update, change or alter anything! I hope you enjoyed this tip, I certainly was excited to put this solution together. There are so many different ways you could alter this solution, using different dates (swap in fiscal calendar dates), add different final total or percentage columns at the end, my mind keeps coming up with new solutions, and I hope you can use this as well!

You can find the full solution in this PBIX download which includes the sample data set.

If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.

As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:

After many requests, we are now selling out layouts unbranded so that you can use them in all your business applications. Be sure to check out our first offerings and stay tuned for more to come in the future. Learn more about Layouts.
July 11, 2019
Quick Access Toolbar for the Win
I’m a lazy engineer. Let me qualify my statement. In lazy I mean I like to find the path of least resistance, the shortest distance between two points. Everyday I challenge myself to be lazy, or efficient how ever you want to view it. As I have built many reports in Power BI over the years here is a fantastic trick, I use almost every day, Customizing the Quick Access Toolbar.

Here are a couple reasons why I think this will speed up your report writing.
- There are only a few shortcuts that I am aware of for Power BI Desktop, thus making your own shortcuts with Hotkeys is faster for repetitive tasks.
- To achieve a simple task, you might have to jump between ribbons, again adding time.
- When you set up Hotkeys you don’t have to remove your hand from the keyboard and wiggle your mouse around then come back to the keyboard. Again, wasting time.
Enough reasons, if you are still reading this then clearly you are lazy like me. Let’s just get to the good stuff…

By default, you get the following Quick Access Toolbar:

The icons are Save, Undo, and Redo.

While these icons are good, YOU DON’T NEED THEM… lol

You can automatically save by using Ctrl + S

Undo is simply Ctrl + Z

And Redo… lets be honest no one uses this one.

Another aspect of the Quick Access Toolbar is when you hit the Alt key you can reveal numbers on the toolbar:

Pressing the number or letter triggers the next button press.

Example:
- Alt + 1 will automatically save the file
To take this further you can string together multiple key presses to dig even deeper into the menus

Example:
- Alt + H + ii brings up the import image dialog box
- Alt + G + D opens the menu to get data
- Alt + W + C Opens the Selection Pane, Repeating this command closes the Selection Pane
The downside with multiple commands it takes more time to learn the key strokes to be fast. As a personal preference I like to use one-step key press options (Ctrl + s or Ctrl + z). It is easier for my simple mind to remember and commit to memory. If you use Adobe products for images or video, you will be familiar with this technique. From my opinion Adobe has some of the best most useful shortcuts that enhance productivity.

Make it Custom

Let’s begin customizing our menu.

First Click the Down Arrow at the far right of the toolbar.

Click on the item in the dropdown menu called Show below the Ribbon. This will move the toolbar to the bottom of the ribbon.

For each item in the dropdown list Uncheck each item, Save, Undo, and Redo. This will remove all the items from the toolbar.

The Quick Access Toolbar will now look like the following image:

In our next step we need a hidden toolbar to appear, the Format ribbon. This Ribbon only appears when multiple elements are on the page. Thus, we will need to add some generic shapes to the page to reveal the toolbar.

Add three Shapes (Squares) to the report page. Click on the Home ribbon and select the Shapes icon listed in the Insert section of the ribbon. Then Select the item in the drop-down list labeled Rectangle.

After adding one rectangle select the rectangle with your cursor. Use Ctrl + C to copy the shape. Then, use Ctrl + V to past Two more rectangles on the page. Use your cursor and move the shapes across the report page. The order of the shapes does not matter, they just need to be distributed across the page. See below for reference.

Use Ctrl + A to select everything on the page.

Notice a new ribbon appears, the Format ribbon. This is called a context aware ribbon. This has been a long standard best practice in office products. Only show the ribbons that are needed. Thus, the format ribbon and the Data / Drill ribbons are not exposed until you have visuals selected on the page.

Click on the Format ribbon. Click the button called Align in the Arrange section of the Format ribbon. Then Right click on the first item in the align menu called Align left. Upon doing this, a menu will appear. Select the first item in the list called Add to Quick Access Toolbar.

Repeat the same process by adding the Distribute Vertically action from the Distribute button on the Format ribbon.

Next, Add the Align top action from the Align button on the Format ribbon.

Finally, Add the Distribute horizontally action from the Distribute button on the Format ribbon.

Using the combination of Aligning an Edge / Distribution is just so much faster then moving one object at a time.

For example, here is a random arrangement of 5 shapes on a page.

Using our newly created shortcuts I select the Items I want to move.

Then using our new key combo, Alt + 3, Boom all the top edges are aligned.

Then the magic, press Alt + 4 and all the items are evenly distributed

Superfast and efficient, aka lazy..

Side Note:

When I am training new users in building Power BI reports, I like to point out the following, it is important to align your elements on the page. This follows along with one of the Gestalt principles, symmetry. Your eye can perceive slight differences in objects that are misaligned. I can’t tell you the number of reports has something like the following:

The change is subtle but your eye picks it up. When these out of alignment objects are near another visual with a straight edge it is even more apparent.

(Steps off the Soap Box) Ok, enough diversions. Let’s finish with adding two more items that I use every time I build a report, Selection Pane, and Bookmarks Pane. We can add these items to the menu as well.

Navigate to the View ribbon (or hit Alt + W if you want to be fancy).

Right Click on the item called Selection Pane. Select the item in the dropdown Add to Quick Access Toolbar.

Do the same for the Bookmarks Pane. When you are finished your Quick Access Toolbar should look like the following:

You may be asking yourself… So why did we move the menu to the bottom of the ribbon? We could have just left it on the top of the screen. You would be right. But, I do want to point out one odd behavior when the Quick Access Toolbar is at the top of the ribbon.

When I have multiple visuals selected on the page the contextual menu appears. See below image with the Quick Access Toolbar at the top:

When this happens any items to the right might get chopped off, and you must access them using the double sideways arrow. The Alt + # keys will still work even though the items are hidden, but I don’t like the experience of having some of my menu items hidden. If you had many icons distributed across the Quick Access Toolbar, say 9 of them, you would have to remember its position in the alt keys. This is the reason why I prefer to show the Quick Access Toolbar below the ribbon. When the context aware menus appear the Quick Access Toolbar is not covered.

Thanks for reading along. Have a great day and I hope this tip / trick makes you a little bit more productive!

If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat
July 3, 2019
Make Custom Visuals with no Code

Welcome to another installment of building custom visuals with the Charts tool from PowerBI.Tips. In this edition we make the following custom visual.

Custom Ribbon Chart

This chart comes to us from the sharp mind of Nick Snapp, Check out is work on Charts.PowerBI.Tips within our custom visual gallery. This chart combines the ability to see both the relative size of values between states as well as distributions of data with adjacent number lines. Pretty cool don’t you think?

Below is the walk through video on how to build this custom chart.

Building the Custom Visual

If you want some other help making custom visuals check out this other tutorial building a simple bar chart.

If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat

June 20, 2019
New Layout – Square One

“Square One” utilizes the color theme as a background component that adds a pop of accent color only. This gives you the end user the maximum flexibility to incorporate color themes that match your needs without drawing to much attention on the main part of the page. Click here to download

The icons are part of the background so that the colors come through with the icons, but this also keeps the same flexibility in that if you have different icons, you can always place those behind the clear buttons placed on the report. We’ve only tied in 3 of the icons to buttons and bookmarks as the others can be added by you as you develop and build out the report.

The idea behind the icons on the right follow two different thought patterns.

First, a Summary, more granular, and then table views of data. In this vein, you would create a clear bookmark to re-direct to the different pages showing that level of information.

Second, would be to create toggles on each page to view visuals in current state vs. over time and use the icons to flip between the two.

Let us know your thoughts on this new layout idea, we hope you get a lot of use out of it!

Sample of Report:

May 1, 2019
New Layout – Smooth Operator

Hey everyone! We’re excited to release our latest layout “Smooth Operator”. Click here to download. We’ve gotten some great feedback from our users and the previous layouts were a bit heavy handed as we tried to create a perfect experience with adding visuals. The difficulty of trying to link up a dataset to the x/y axis made some of those a bit burdensome. As a result, we’re going to streamline the layouts to be super easy to use but yet still providing as much enhanced value with bookmarks, areas for visuals, and using json themes to change look and feel of background and visual elements.

We’re interested in your thoughts on this streamlined approach, so please let us know how you like the new layout – and expect more like it in the future!

Sample of Report:

March 21, 2019
Make Custom Visuals – Create a Bar Chart

With the release of the custom visuals building tool Charts.PowerBI.Tips we received a number of comments requesting tutorials on how to build visuals. Ask and you shall receive! Below is a basic tutorial on how to create a Bar chart. Within this tutorial we review a couple of the features of the chart tool and how to use them.

Video Tutorial on Building a Basic Bar Chart Custom Visual

Comment below on other topics you would like to see.

Be sure to follow:

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe on YouTube. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

February 12, 2019
Respect Layers

If you are like me and you like making your reports look extra good with different visual elements you’ve probably come across the issue before where you use shapes or images layered behind a visual. What you have quickly discovered is that an end user can mistakenly click on that background layer and the entire object pops from the background to the foreground. In this quick tutorial, we’ll show you how to STOP this from happening! We learned the technique through a webinar given by Miguel Myers and wanted to make sure we spread the word because it will have a huge impact on how we can build reports!

Be sure to follow:

If you like content from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to us on YouTube. Or follow me on the social channels, Twitter and catch up with me on LinkedIn where I will post all the announcements for new tutorials and content.

December 4, 2018
New Layout – September 2018 Blog Layout

In the September 2018 blog post the Microsoft team released a new layout. This layout has a number of really nice design elements. However, upon reviewing the file used for this demo found here. Upon downloading we noticed that the new style of the layout was only one page deep. As an enhancement to this file we added all the pages, renamed all the elements and created a full PowerBI.Tips layout from this page. We’d love to share our work with you and hope you enjoy this new layout from PowerBI.tips.

Be sure to download your copy here:

Demo of September 2018 Layout:

Be sure to follow:

If you like the content generated from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.

October 2, 2018
Using Grids to Improve Visualization Placement

When you design a report, there are a number of things to consider. For example, the types of visuals, the colors used within the visuals, and the location of the visuals. The orientation and alignment of the visuals is a subtle but important aspect of your report build. Doing a good job aligning the items removes distractions from the report page and allows users to engaged with your data story.

To that end, using grids in Power BI desktop has been extremely helpful to me to aid in aligning elements on the page. In this tutorial, I walk through how to use the default Grid settings of Power BI. Additionally, I developed a couple of grids as images that can be used to aid in aligning visuals. Check out the video below to see how you can use Grids, and download the new Grids Layout for your reports.

Tutorial Video:

To download this layout follow this Link, or use the product image below:

[product id=”18774″ ]

Be sure to follow:

If you like the content generated from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.

September 4, 2018
Microsoft Business Applications Summit – New Layout – Purple Haze
In honor of the 2018 Business Applications Summit, PowerBI.Tips has published a new layout, Purple Haze. Purple Haze is the brain child of Seth Bauer, and utilizes the latest features of the July 2018 Power BI Desktop.

In the previous versions of Power BI desktop there was always a header present at the top of a visual. Now, the header is no longer needed, and design elements within the report can extend all the way to the very top of the report page. This is great news for report designers and new possibilities for Layouts.

Hit the link to download the Purple Haze layout.

[product id=”18324″ ]

Cool Features of Purple Haze:
- The report comes with four (4) pages. In the example the are listed as Accessories, Bikes, Clothing, and Components. By Clicking on these buttons the report navigates to a new page where all the visuals for these topics exist.
Purple Haze Menu Bar
- The filters across the top of the report are using an upper third filter context window. This window is exposed when clicking on the filter icon, which is found in the upper left hand corner of the report:
Purple Haze Filter Menu

We hope you enjoy this new free layout.

Be sure to follow:

If you like the content generated from PowerBI.Tips please follow me on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to me on YouTube. Or follow me on the social channels, Twitter and LinkedIn where I will post all the announcements for new tutorials and content.
July 30, 2018