We are starting today off with a fun chart. We will be making a filled donut chart. Typically, I don’t use donut charts but in this case I think we have a good reason, and it’s delicious…
The data being used in this visual varies from o to 100%. This could be something like a test score or a compliance number. Thus, we will be using the donut chart to represent a completion of 100% or some variant below.
Video on how to build this chart.
During this video we used a couple measures. They are the following:
Measures
Average Product Score = AVERAGE( 'Product Scores'[Score] ) / 100
Average Product Score Inverse = 1 - [Average Product Score]
Where the value of the Score comes from the Product Scores table. The Score column ranges from 0 to 100%. If you like this visual and want to download a sample file visit my GitHub page.
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:
As report authors we sometimes get caught up in how easy it is to create a report and provide value to the business. Each report is an opportunity to make a big contribution to the organization. Power BI makes it easier than ever to turn many of those reports around quickly. This is a good thing of course. But, sometimes we can get caught up in the madness of turning out another report with only a flash of recall that we could have used the same or similar model done in a different report. The internal monologue kicks in.
“Pffft! Re-use a model, that is way to much work! Why do that?, when we can just create a copy of the PBIX (Desktop file) and have two models to manage with the two reports for the same business area! That sounds like fun!”
Hmm… Or does this actually sound awfully similar to the challenges one might face with sprawling Excel solution. Where variants of logic are buried in different files. Only the composer knows how to bring order from the chaos. Might I suggest that we spare ourselves that sort of pain. Just learn how we can easily leverage our already hard fought model work. Avoid tedious updates without having to over complicate our nirvana of sticking to the business world, but how would we do that?
The Answer
Power BI datasets. Power BI datasets allow us to re-use our model across multiple reports. This simplifies and speeds up future report authoring. Also this gives us the building blocks for sharing that dataset to a wider audience.
How do we create one?
Technically, we’ve likely already created many. You see, when we publish a Power BI report, we publish a dataset along with it. This dataset is stored in the Power BI Service, and our deployed report relies on it now. But the connector “Power BI datasets” allows us to connect directly to any of these datasets that we have permission to edit. This means that we have the ability to extend a single model across multiple reports without the need of standing up a separate Analysis Services server anywhere. This is a big deal, this allows the everyday business user to leverage a reusable model. A single change or update to a calculation can update multiple reports at the same time. One measure or calculation addition can be done in one place instead of many.
All we need to do to create a dataset that we can connect to is publish a PBIX file that contains data. I’ve adopted a practice recently and rather than generating my first report and reusing that model, I now upload a PBIX file that ONLY contains the model and I name it something like “Sales-Model”. Now I have an object that I’ll know serves the purpose of just being a model instead of a report. This makes it easier from a trace-ability standpoint when looking at the related objects in the Service or selecting it from my list of options when choosing my dataset.
How do we use one?
Using the Power BI dataset is one of the most
straightforward connections in Power BI. Selecting Get Data -> “Power BI
datasets”
This brings up the menu of all the datasets in the Power BI Service. The list that is shown are the datasets that our user account has access to use. The great thing about these datasets are we now have the ability to connect to and use a dataset from a different workspace provided we have permissions to edit them. This feature is called a Shared Dataset. Select the dataset and your report is automatically connected to dataset.
Now, what we’ll notice here is that using this feature automatically pulls in a model for us and we can start building our report. This data source connection behaves exactly the same as if we created our own “live” connection to an Analysis Services instance we would set up. Probably not shocking to any reader here. But, that is exactly what is happening in this case as well. We get the benefit of Microsoft handling all that painful work for us while we reap the benefits of a streamlined process.
As with any “easy button” solution, there are pro’s and con’s. What I mean is that in our new reports we do have easy access to the model. Now you can start building reports immediately. We don’t have the ability to modify the model or the ETL processes. If we want to edit then we need to go back to the original dataset to make those changes.
But the minor inconvenience of having 2 PBIX files open if we need to in order to make updates to the model is trivial compared to being able to connect many reports to that single model. The live connection does still allow the report author the ability to create measures. So, if there are measures that are only suited to our report and not the overall model we still have the ability to add them.
Once we’ve completed
our report, we just publish as we normally would, only this time the dataset is
already out in the Service and only our report is published. There are so many
things we can now do to share that dataset, but we’ll leave that to another
article.
If you’ve never used this method before, I would highly encourage you to try it out. Any time you can save yourself now with reducing the number of models you maintain, the faster you can produce more reports. You now spend less time maintaining all the reports you are publishing.
Happy report building!
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:
This post will answer how to sort a measure that returns text values to a custom order, without affecting other columns. It will utilize the DAX functions of REPT() and UNICHAR(8203) – a Zero width space.
The requirements
I’ve been working at a florist! In this example, I have been in charge of looking after four plants, named A, B, C and D. The florist owner is a big Power BI fan, and asked me to measure how much water I have been giving them a day to put in a report. They need at least 20ml to survive, but over 50ml will stop them growing as well.
Create a table with the flowing: The flowers get under 20 ml, label as Bad. When the flowers get 20 – 50 ml, label as Good. Finally, if the flowers receive over 50 ml, label as Warning. I’ve been asked to show them in order of Bad, Warning then Good. This is vital so the plants needing attention are at the top of the table.
Creating the table
Here is the measure I create:
Adding this to a table:
Now comes the question, how can I order this to put Bad and Warning together? If I order by Water Target measure, this will be alphabetical. Sorting by WaterIntake can not give me the correct order either. One option would be to make a conditional column and use the “Sort by Column”. However, this may be a complicated calculation, especially on more complex measures. In addition it will sort every visual by this column, when I only want to sort in this one table.
Creating the custom sort
My solution? Make use of the UNICHAR() function. For those unaware of this function, UNICHAR() can return characters based on their UNICODE number. This can include more text characters not included on the standard keyboard.
A character that can help is UNICHAR(8203). This is a “Zero width space”. This is a space that has not width, so it is essentially invisible and will not be visible in the measure. The Zero width space is still recognized as a character by DAX. Spaces come before any letter in the alphabet. Two spaces comes before one, and so on.
The second function I will utilize is REPT(). REPT() or replicate, simply repeats text multiple times. It takes two arguments, the text and the times to repeat.
For example: REPT( "Hi", 3 ) will return the text "HiHiHi"
To change the sort order, I will repeat the Zero width space in front of the text. The text I want to appear first will have the space repeated the most amount of times. This will put it first in an alphabetical list. I will use the & symbol to concatenate the Zero width spaces and the text.
Now, “Bad” has the Zero width space repeated three times in front of it. This now puts it first in an alphabetical list. Warning has the Zero width space repeated twice, putting it second. “Good” has it once putting it third.
Applying the sort
Now I can arrange my table by Water Target (alphabetical), in an ascending order:
And success! I’ve added a custom sort to my text measure, without making any other measures or columns.
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. 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:
The release of grouping visuals was an extremely welcomed
feature. As one who builds lots of reports grouping elements together is
essential to stay organized and to increase report building speed. Since I’ve
been using this great new, I found an interesting design element to style
groupings for reporting impact. The grouped visuals feature enables a new
property, background color. This can be
applied for the entire group of visuals.
See the following example of setting a background around two
visuals.
In this example the intent is to show the user that these
two visuals are related. The graph on the left shows the number of units sold
for a selected time period. The bar chart on the right shows the relative sales
over time represented as a percent change. This illustrates the principle of
position and direction. The number of units sold is what happened right now. It
is my place in time with respect to sales. However, this does not show any
context to performance. The percent change provides the directional context. Since the position and direction are an
important insight as a paired visual, we use the grouping to visually bind the
two.
For those who have done some research around design
principals inevitably you will stumble across the Gestalt
Principals of design. Grouping
visuals with a common background falls into the Law of Common Region or Law of
Proximity.
Alright let’s walk through how to use grouping with
backgrounds colors.
Once you have created the visuals which will be grouped together;
select each visual by holding CTRL and Selecting each
visual.
Right Click on one of the visuals and select the menu
item labeled Group, in the flyout menu select the option called Group.
A grouped element will be created in the Selection Pane.
Note: If you don’t see the Selection Pane, you will need
to turn this on. The setting to turn the
Selection Pane is found in the View ribbon with the check box for Selection
Pane. See below for reference.
With the newly created group being selected, Click on
the Paint Roller (Format) icon in the Visualizations Pane.
Expand the property section called Background.
Toggle the background to be On and select a Color from the
drop-down menu. For this example, I
selected the very first shade of grey in the first column of colors.
The final product will be a grouped arrangement of visuals
with a shaded background.
To extend this idea further we can take the same approach
when working with Text boxes and Visuals.
Often, I find I need more style for applying a Text box or header to a
visual. In these cases, I will use two
visual elements to create one visual.
See this example of two visuals with custom titles created with a
textbox.
Note: Backgrounds are colored differently to illustrate
that each background for the grouped visuals is different.
While this meets the need the boxes are not identical in
size. This violates yet another Gestalt
Principle, symmetry. The bounding
regions of the elements inside the grouping define the outer perimeter of the
background shading. Knowing this we can
modify the visuals within the groups to provide a symmetrical background shape.
Here are the same before and after images with each visual
object colored to see the adjustments in size for each visual type. This creates the proper background
sizes.
Before:
After:
The visual on the left required an increase of the text box
at the top to get the desired width of the background shape. By contrast the visual on the right required
an extension of the bar chart in length to acquire the desired length of the
background. The result provides a
symmetric view of both visual groups.
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:
Ever need two different scales on the Y-Axis of a line chart? If so, then this tutorial is for you. While creating a dual y-axis line chart is pretty common in excel, it is not as easy in power BI. The only standard chart that comes with Power BI Desktop that enables dual y-axis is the Column and line combo chart types.
For this particular visual I needed to show correlation between two time series with different Y-axis scales. The Y-axis on the left of the chart had data elements in the thousands, but the right side needed percentages. The tutorial below illustrates how to accomplish by building a custom visual using the Charts.PowerBI.Tips tool.
Video Tutorial
note: there are a bunch of really good custom visuals that can be downloaded from the Microsoft App Source store. However, this article will not review all third party visuals that are able to produce a dual Y-axis line chart.
Source files
All files used to create this visual are located here on GitHub.
Layout file
The file used in this tutorial was a derivation of the Sunset layout from PowerBI.Tips. If you like this file, you can download it here:
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:
This layout continues to deliver fantastic visual guides to make your reports look top notch. This layout utilizes buttons for navigation without locking in the position in the layout background. We also really like how this layout uses the theme templates to change the background header color to anything you would want. The semi-circle that indicates which page you are on is a free form image and can be changed around if you want to re-arrange the pages. Our branded layout gives you 5 pages of fun, while our unbranded version throws in 10 pages and includes all 10 background .png image files to make your report building even easier.
Features of this report
Free documentation provided by PowerBI.Tips included on the Report Features page
10 pages of different layouts – Unbranded
5 pages with navigation built with buttons in the report (Easily swap out a different background)
10 PNG images of all the backgrounds to use in this report or others – Unbranded
Navigation dot – included icon image for complete flexibility
Customizable top ribbon (color of buttons and background header are can be altered with themes)
No PowerBI.Tips Branding on any of the main report pages – Unbranded
One – Capabilities
Get a feel for all the page layouts and interactions available in this report by using the below example we’ve embedded via Power BI.
Sometimes, we want the users to see different metrics, but
do not want to take up too much space on our page. The scenario we are going to
walk through is how to build just one visual (in this case a bar graph). It
will include a toggle that allows the user to select their desired calculation,
either the sum of Volume, Dollars or Margin.
Final Solution
With buttons, we can change specific visuals on a page. Recently,
with the release of conditional formatting on titles and backgrounds, we have
some new methods to make this easier for the report author and cleaner for the
report consumer.
The Build
Before we start, turn on the selection pane and bookmark
pane. They can be turned on by clicking on the View ribbon and checking the
correct boxes.
First, we’re going to create our control table. This
will be a disassociated table. This table should not have any relationships to
any of the other tables in our model. We just need to enter a numeric ID and a
description of what we want. Click on
the Enter Data button found on the Home ribbon. Enter the
following data as shown. Click the OK button to close the Create
Table dialog box.
Now that’s set up, we can write our measure. This measure will see what is selected in the Number_ID column of our control table, then return the appropriate calculation. Use a switch statement to select the correct calculation. Create the following measure:
Note: See there is a default value listed in the switch
statement. The default calculation means that if nothing is selected, SUM(
Sales[Volume] ) will be returned. The default value is represented by the last
property in the switch statement.
Time to set up our visual. Add a bar graph with Category on the
axis and the new measure, Selected Calculation, in the values
fields. Then add a slicer for the Number_ID column. The Number_ID
column comes from the control table we added earlier.
Switching the slicer can now change the graph to show the
different calculations.
The next stage is to add three buttons to the top of the
graph. In the Home tab of the ribbon, click Buttons and select Blank. Make sure
the outline colors and outline width match on all objects, Buttons and chart
outline.
Tip: Make sure you label your buttons in the Selection Pane. The selection pane can be turned on by clicking on the View ribbon and checking the box labeled Selection Pane. To Change the name of the button, double click the name listed in the Selection Pane. Giving a title (such as Button_Volume) will make it easily to see what visual items are on the page.
After this, it’s time to add the bookmarks.
The bookmark pane can be turned on by clicking on the
View ribbon and checking the box labeled Bookmark Pane.
Step 1:
Select a value of 1
in the Number_ID slicer.
Select the slicer (and only the slicer) in the
Selection pane.
Click “Add Bookmark” in the Bookmarks pane.
Step 2:
In the Bookmarks pane, right click the bookmark and rename it to Select 1.
Right click again, and untick “Display” and “Current Page”. Select “Selected Visuals”.
Now repeat step 1 and step 2, but do so with the values of 2 and 3 from Number_ID
slicer. Name these bookmarks Select 2 and Select 3. You should finish with
three bookmarks, each that filters Number_ID to a different value. You
can test the bookmarks by clicking on them once in the bookmark pane.
On Button_Volume, assign the Select 1 bookmark (as Number_ID
1 refers to volume). To do this, click on Button_Volume in the selection pane.
In the visualizations pane for this button, go to the property named “Action”.
Turn it on, change the type to bookmark, and choose Select 1 in the dropdown.
Repeat for Button_Dollars and assign Select 2. Then
for Button_Margin and assign Select 3. Now the buttons can change the
graph, but it’s a bit hard to see what is selected.
Add Conditional Formatting
This is where conditional formatting can help us! Select Button_Volume
in the selection pane. Then in the visualizations pane, turn on the background
property, select the ellipsis and click conditional formatting
Here’s the settings we want:
This is going to apply a rule if the Number_ID selected is 1, to give the button a blue background. As there are no other rules, any other number selected will default to the white.
Now, apply the same steps to the other two buttons, but make
the rule “If value is 2” for Dollars, and “If
value is 3” for Margin.
To tidy up, hide the slicer and turn the visual headers of all buttons off. You can click on the eye next to the slicer in the selection pane to hide it.
Turn the visual headers off by clicking the button, then in
the visualizations pane.
Great! Now the tab shows the selected button and correct
measure:
To make it even clearer, apply conditional formatting to the
title of the graph. On the graph, open conditional formatting. Set it to field
value and use the type field in the control panel.
Using this control table allows for greater flexibility. We can add more calculations, easily edit them or even sync across pages, all without having to re-record any bookmarks.
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. 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:
I am just bursting with excitement!! This month the amazing Power BI team has yet again come out with a great new feature, Icon sets. In addition to this you can enhance these icon sets by adding your own custom icons to your Power BI reports. Woo Hoo….
So what does this mean? Well, now you have a new Conditional Formatting box found in the settings of the Table and the Matrix properties. To use a built in Icon from Power BI. Create either a table or a Matrix visual with some data.
Select the visual and adjust it’s properties by clicking on the Paint Roller and opening the Conditional Formatting window.
Scroll down until you see the toggle button for Icons. Turn the Icons On.
Click on the Advanced Controls to set the properties of the icons based on the data properties. This type of dialog box should look familiar as it is similar to the previous boxes for conditional formatting. Opening this window shows Icons for each Rule in the list. To adjust an icon Click on the Drop DownArrow next to the icon you wish to change. There are multiple icons to choose from.
There are limited selections by default, but you can enhance this by adding your own icons with the custom Json theme files. At PowerBI.tips we love our theme files. They make using standard settings so much easier.
Loading the Custom Icons
For starters we have already done the hard work of creating an additional 50 icons for you to use in your reports. Download the Icon Theme File Here
Update: Special thanks to Reid Havens from Havens Consulting for contributing extra icons to this Icon Set.
Unzip the downloaded file to find the PowerBITips Icons v1.json file
Navigate to the Home ribbon in Power BI Desktop
Click on the Switch Theme button
Select the list item Import Theme from the drop down menu
The open file dialog box will open. Select the PowerBITips Icons v1.json file that you downloaded earlier.
Boom, and just like that you have loaded your new icons. Now you can return to the icons for your table or matrix and adjust until your heart is content.
Here is a sample of a table and a matrix with some custom icons applied:
Update 2019/08/06: When publishing the Power BI file to the PowerBI.com service, the fill colors for the shapes need to have a %23 instead of a # (HASH) infront of the HEX codes. Thus, the format should look like fill=’%23FF0000′ instead of fill=’#FF0000′
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:
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.
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”.
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.
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:
