You are reading the article Power Bi Reporting Templates Expanded – Power Bi Visualization Concepts updated in November 2023 on the website Moimoishop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested December 2023 Power Bi Reporting Templates Expanded – Power Bi Visualization Concepts
In this tutorial, I dive into how to customize Power BI reporting templates efficiently. You may watch the full video of this tutorial at the bottom of this blog.
When you create tables inside Power BI, you can’t format or design them exactly the way you want them to be. You can only use the default order or sequential order that Power BI generates within the table, which can be determined by a value, by alphabetical order, or an ascending or descending order.
That’s why I developed Financial Reporting Templates. These are a development concept created by Enterprise DNA. With this technique, you can now create customized financial reporting templates using tables inside Power BI.
This tutorial session is taken from a much longer and more detailed Enterprise DNA member-only workshop. Here, I demonstrate key concepts around how to visualize financial information and data effectively in Power BI.
First, let’s have a look at this template that I made for financial reporting.
Financial reporting is a perfect example for this demonstration. However, there’s a lot of custom templates or custom tables that you might want to create with a variety of information, such as a summary table of your key metrics.
In the past, this was very difficult to do if you wanted to grab specific measures out of different measures and integrate them into one report and put them on top of the other.
Now in this sample template I’ve broken it down by Total Revenue, Cost of Goods Sold, and even more, which is where the true customization comes in. I’ve left a space in the table, then I have Total Gross Profit.
I’m integrating a percentage with dollar amounts as well, which is again with a customization.
Let’s get to how I created it…
To get to this point of having to customize Power BI templates, it requires a lot of re-configuring in the background, the model set up, and the technique to grab revenues and costs, and then integrate them into one.
The first thing to do is to actually create the template – a unique table that looks somewhat like this – but of course you can customize in whatever way you want. You can put different columns or have some items indented in a different way.
You also need a Normalized Column, where we’re going to run a logic through. We’re going to identify which line we’re on in our custom template based on this normalized column.
So this is the set up of a customized template, but then you need to find a way to integrate this into your report. This is where the data model comes in.
The structure of the data model is key to integrating your customize template into your table. As you can see in my data model, the Financials Template is right at the bottom and it has no relationship to any other tables.
This is where I put my template column and use a formula to work it out at every row in the table.
So let’s have a look at the DAX formula I created to solve this.
I used the SWITCH TRUE logic in the formula, which enables me to integrate the customized template that I made into the table. It works out at each row in the table, identifying the items.
This is what the SWITCH TRUE does. For instance, if I’m on the Total Revenues row, the calculation DIVIDE Revenues by 1,000 is applied. If I’m on the Total Cost of Goods Sold row (COGS), it will show DIVIDE Revenues by 1,000 etc.
It’s the SWITCH TRUE logic that enables the allocation of results to the rows in the table.
The final piece of the puzzle for this Power BI template technique is the Row Index, which sorts out the template in the table.
If you don’t have this, it’ll get confusing and won’t sort the items in a unique way. And what I did was, I hid it so you can’t see it in the table, but it’s actually there.
If you look closely, you can see that I have the Row Index and I’m able to sort 1, 2, 3, 4, 5, and so on. That’s how I get the exact template set up as I want.
This tutorial is a breakout session from a members-only Enterprise DNA workshop around financial insights. Here I focused on the technique that I developed to customize Power BI reporting templates. The technique involves the integration of data model, DAX formula, and template creation.
I hope you able to understand how valuable this concept is and implement it in your own business. You will find it helps a lot with your financial reports and any accounting presentations you might have.
Cheers!
Sam
You're reading Power Bi Reporting Templates Expanded – Power Bi Visualization Concepts
Handling Http Errors In Power Query And Power Bi
If you are working with web servers, either because you are trying to scrape data or you are using a web based API, you will be sending and receiving data via HTTP.
HTTP is the Hypertext Transport Protocol – it’s just the name of the system used by web sites to transfer data. You use it every time you visit a web site
If your request results in an error, the web server (or API) will generate an error which is returned to Power Query.
The default behaviour is for Power Query to then spit out a message like this
If you are not familiar with HTTP or this type of error then this can be confusing. What exactly is the problem?
This Works In Power BI and Power Query in Excel
I’m going to do this in Excel but you can do the same in Power BI. The main query uses exactly the same code in Excel and PBI. But the method to create the static data table of HTTP Error Codes is different.
In Excel I use #table and in Power BI I use the Enter Data button on the Ribbon. To read more about the different ways to enter static data check out this blog post Static Tables in Power Query, Power Pivot and Power BI.
Watch the Video
Download Sample Files
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.
Power BI Desktop File.
If you could handle this type of error in your code and provide a little more information to the end user, perhaps that would help them troubleshoot the issue and resolve the problem.
What if your error message was this
The message gives the user some idea of things to check (spelling) and tells them a way to confirm the URL is correct (type it into the browser).
Looking at the query, the URL it’s trying to access is
Typing this URL into my browser gives this error
You may have already spotted that the URL is incorrect. It should end with microsoft-365 not microsoft-356
By giving a more informative error message and some steps the user can take to troubleshoot, we can help them fix, or at least understand problems that may occur.
Manual Status Handling
Manual status handling means you are going to write your own code to deal with errors. You have to tell Power Query you’re going to handle them and you do this by specifying the ManualStatusHandling value when you make the Web.Contents request.
Web.Contents
Only Web.Contents allows you to manually handle HTTP responses. Neither chúng tôi or Web.BrowserContents support this ability.
For example to tell Power Query that you will deal with 400 (Bad Request) and 404 (Not Found) errors the request would look like this
Where the list of error codes you’ll handle are specified as a list of numbers {400, 404}
Handling errors means that you need to write you own error messages. To store these I’ve created a static data table that stores the error codes and the messages I want to display should the associated error occur.
The table is stored in a query called HTTP_Errors and looks like this
The ErrorCode column is a list of the error codes I’m handling so I can change the Web.Contents request to reflect this by replacing the { 400, 404 } list.
To check if an error has occurred you can use the Value.Metadata function
This gives you data like this, and I’m interested in the Response.Status
You can see the web server has responded with a 404 error. To access this value directly you can do so like this
Now that we can get the response code from the web server, we need to check if it is an error we want to handle. To do this you can use List.Contains to check if the ResponseCode is in the ErrorCodes column of the HTTP_Errors table.
If the web server’s response code is an error we want to handle then the code needs to display the associated error message.
To access the error message, first I’ll use List.PositionOf to get the row number for the error code.
Because table columns are lists you can use list functions on them. Lists are indexed from 0 so error code 404 is on row 3.
If the web server’s response is not an error I want to handle then the code will just return the response as it is.
Putting this all together the code looks like this
If an error occurs that isn’t listed in my HTTP_Errors table then Power Query will deal with that in the default way.
If no error occurs then the Response step contains the web server’s response and further transformations can be carried out on it.
Current Trend Analysis In Your Power Bi
In this unique example, I want to show you how to project a historical trend in Power BI and repeatedly project it forward. This particular example came from a question in the Enterprise DNA support forum. You may watch the full video of this tutorial at the bottom of this blog.
An Enterprise DNA member needed to carry out this analysis in the real world using historical information, which could be for example, quarterly or half-year results. They then had to take that older information and project them forward for some years.
This is an excellent example of how you can reuse time intelligence functions in many different ways.
You can also enable this logic to be filtered by a particular region, a specific store or for a particular customer set.
The visualization for your trend analysis is practically useful when you have a grouping, a quarter, or a sales period and you want to project or predict the results going forward.
This solution that I have discovered can be applied in a number of different ways. The current example may be about predicting a quarter, but you could project anything continuously going forward using a similar technique.
There’s a lot of interesting ways to apply a trend analysis inside your Power BI report. Now, take a look at this table below.
Firstly, you can see the columns for the specific dates of the year and its corresponding total sales. But, I also need to showcase what day it is in a particular quarter. That is why I created the Quarter Day column.
I’ve gathered that data from the Dates table. In this table, you can see the Quarter Day column. Let’s see how I worked this out.
A quarter day is a significant figure in business. It signifies the day that officially begins a three-month period of the year (a quarter). It is important to really work out this dynamic calculation of quarter day since I intend to project its trend every single quarter in every year.
So, in order to find out the quarter days, I used the formula below.
It’s just going to find any particular date, and then subtract it to the very start of quarter. Lastly, just add 1.
As I go down the Quarter Day column, you’ll see that it continues to go all the way up until about 92. And then, it goes back to 1 at the very first start of the next quarter.
Now that I have calculated the quarter day numbers, I need to decide on the particular time frame that I want to project forward. To do that, I need to use the formula below for the Quarterly Forecast.
First, I use the CALCULATE function for the Total Sales.
The FILTER function removes ALL the filter in the Dates table initially, and then re-applies them to a very specific time frame, which is 2023 for year, quarter 3 for quarter, and the very same day of a particular quarter.
The MIN function actually evaluates to the quarter day of every single day as we move through any month into the future and evaluates their correct quarter day.
So in any particular day in the future, it will always jump back to the 3rd quarter of 2023, on the same quarter day.
In this example here, I’m showing a very specific time frame instead of just repeating historic numbers. In the example visualization below, I’m actually comparing the quarterly forecast to the total sales.
The graph seems a bit busy so you can actually improve it a little bit. This is just one way to see it in a comparison perspective.
Aside from that, you can also use a moving average measure inside it. This can help users to calculate moving averages of a specific time frame, and then just continually project that out.
Moreover, you can predict anything out aside from sales and that’s where measure branching comes in. Actually, you can also add factors and even percentages for your particular results.
To sum up, all these trend analysis can be done very efficiently within Power BI when you set up your calculations correctly.
The great thing about this technique is that I feel this is such a real-world application in Power BI. Likewise, it’s also about how efficiently you can create this sort of logic using DAX formulas. Moreover, you can showcase it in dynamic ways when projecting your trends forward.
Good luck with learning this one.
Sam
Sorting Date Table Columns In Power Bi
In today’s blog, I’ll discuss a question that comes up all the time in the Enterprise DNA forum. How can you can sort the fields from an extended date table columns properly? You may watch the full video of this tutorial at the bottom of this blog.
This refers to the extended date table from the M code showcase that was developed by Melissa Dekorte, one of our Enterprise DNA experts.
Our example on this blog post is just a simple application that runs from 2023 to 2023 with a fiscal year that starts in July. The member wanted to learn how to sort the Month Name; they were also trying to sort one of the fields related to month.
This is how you can sort a date table column effectively.
Our extended date table has a lot of fields in it; frankly, they’re hard to keep in order sometimes.
This is why we created this cheat sheet that shows you what each of the fields looks like, as well as their format and content, on any given day.
In most cases, you can use this to figure out how to sort one field based on another.
We have the Month Name and the forum member was trying to use MonthnYear to sort it out.
Let’s find Month Name in our extended date table. Select Month Name in the Fields pane, and then go to Sort by column, and look for MonthnYear.
This comes down to a granularity problem. You need a one-to-one relationship between the field you’re trying to sort and the field you’re using to sort by.
In this case, for a given month in 2023, 2023, and 2023, we can have three different values for the MonthnYear field. This is why we need to find one that has a one-on-one relationship.
If we go back to our cheat sheet, we can see that MonthofYear is just the number of the month. This will have the one-to-one relationship that we’re looking for. This means January will correspond with 1, February with 2, and so on.
The next one, Month & Year, is a little more difficult. It is a compound field that has a short month and year.
You can see that for the Month & Year table column, everything has sorted out perfectly.
So now we have the first two done. However, the last one is a really ill-behaved field.
It’s problematic for a couple of reasons. We have two text fields concatenated together and the fiscal month field is not padded.
Ultimately, we want to achieve something similar to this MonthnYear, where we have a four-digit fiscal year and then our two-digit fiscal month padded after that.
The easiest way to do this is by using the power query.
We go to Transform data.
And then we go to our Dates table and add a Custom Column.
Let’s call this custom column Fiscal Year Fiscal Month Sort (FYFM Sort).
We’ll start with our text prefix which is 20 for the first two digits of our year and then we’ll concatenate that with a function called Tex.Middle. This function pulls a substring out of a larger text string. It’s analogous to the DAX function MID, and I’ll show you the difference between the two.
We’re going to use this on our fiscal year field.
In DAX, when we pull substrings, it’s a one-based index. To pull the third character, we will need to use 3. In power query, it’s a zero-based index so the third character will be 012.
We start with 2, which is the third character. We want to return two characters to get that second pair of digits off the fiscal year field.
The next step is to concatenate this with our padded fiscal month field.
In DAX, we will need to use a LENGTH function and IF function, then we can manually pad.
In power query, there’s a function that does all of that. This is the Text.PadStart.
Since the fiscal period is a numeric field, we’ll need to use a function called Text.From to convert the numeric value into a text value that we can pad. Then we’ll pull our fiscal period field.
We want to make sure that it has 2 characters. In cases where it has 1 character, we’re going to pad it with a 0.
If we sort our FM & FY field by column, it will now sort perfectly by fiscal year and then a fiscal month.
ConclusionIn this blog, I’ve covered the technique on how to sort date table columns in Power BI. This is a good strategy to do for difficult fields, which came from building a few custom functions. I hope you have found this general set of tools helpful in your own report development.
If you haven’t done so already, please subscribe to the Enterprise DNA TV channel, where we have more content coming out in the future.
Cheers!
Sam
Power Bi Challenge 22: Hotel Revenue Management
There were so many amazing submissions in the recently concluded Enterprise DNA Challenge 21 that we’re excited to see what everyone comes up with as we start the next round.
Out of those 62 entries, we received submissions from 44 non-subscribers and 40 newcomers. These numbers are proof that you don’t have to be a Power BI expert to join these challenges. Quite the opposite, in fact. If you’re just starting out in your journey, the best way to learn is to dive in to real-life datasets and get help from the seasoned pros in our community forum.
This time around, we’ll find out how we can use Power BI to improve hotel revenue management. So if you’re an analyst working for the hospitality or tourism industry, now’s your time to shine!
To refresh your memory, the Power BI Challenge is a series of fun and friendly challenges that all started in our Power BI forum. For each challenge, the Enterprise DNA team releases a scenario simulating what you are likely to face in a particular industry.
The objective is to assess this scenario and using the dataset provided, come up with an actionable report that provides insights and solutions that meet the requirements set out in the brief. The entire community will then come together to review the project and discuss solutions on the forum.
We have covered a lot of real-life scenarios from various industries and niches ranging from Formula 1 to COVID-19 reporting. The sheer amount of support and participation we get for every challenge we post is a huge testament to how flexible and dynamic Power BI truly is.
Here are the categories of winners for the Power BI Challenge 22:
Experts & Previous Winners – entries from the Enterprise DNA experts and previous winners of the challenges
Overall Winner – all entries are eligible, excluding experts and previous winners
First Time Participant Winner – open to any Enterprise DNA subscriber who is taking part in the challenge for the first time
Winning Non-Member – open to entries who are not currently Enterprise DNA subscribers
Out of the Box Thinker – for those submissions that dig deep into their analysis
Creative Head – for those who bring a lot of effort into their report and analysis
The overall winner gets a free Enterprise DNA On-Demand subscription, a chance to be featured at the Enterprise DNA Showcase page, as well as a chance to present their findings in one of our webinars. All category winners also stand to get free subscriptions to the Enterprise DNA On-Demand platform.
And even if you don’t win any of these categories, you still have a lot to gain from joining the challenge:
Get your work showcased across the Enterprise DNA network
Receive personalized feedback on your development
Get experience across different industries
Build an awesome analytics portfolio for prospective employers
If you want to start working on your submission, check out the details for Power BI Challenge 22 here. Revenue management involves the use of data analytics to help those in the hotel industry predict their customers’ behavior. The resulting insights are then utilized to make better decisions in regards to pricing, marketing, and distribution strategies.
Your role as a data analyst is to prepare a deep-dive analysis on the hotel revenue management dataset provided in the brief.
Your report will not be limited by the number of pages or techniques. Feel free to use all the techniques at your disposal – tooltips, drill throughs, page navigations, etc.
Also take note of these client requirements when making the report:
Client does not want an Intro Page but suggests having vertical/horizontal navigation bars or pop-up menu.
No scrollable or fancy shape reports. Preferred size is 16:9.
Client likes KPIs.
Client is very much interested in a time analysis exploration (Seasonality, Festive Periods, Weekday vs Weekend).
Client is interested in an agent performance overview.
Client is interested in customer type information (family with children, single, or couple visitors).
Email the pbix file to [email protected]
Include a brief description of the report in your submission.
If you’re a subscriber, post the screenshot and the Publish to Web URL of your report in the Enterprise DNA forum.
If you’re not a subscriber, post the screenshot and the Publish to Web URL of your report in LinkedIn.
Tag Enterprise DNA and say, “I accepted the Enterprise DNA challenge.” Use the hashtags: #EDNADataChallenges #EDNAHotelRevenueManagement in the social media post.
Power Bi Financial Year To Date Calculations
In this tutorial, I’m going to talk about Power BI financial year to date calculations. Achieving a year to date analysis of a selected period is quite complex, but I’ll demonstrate to you a formula technique that enables you to quickly create a cumulative total over your specific financial year. You can watch the full video of this tutorial at the bottom of this blog.
I’m going to work with a simple model here. It’s pretty generic, but don’t get too caught up in the specific measures. The techniques are all the same especially if you’re doing financial year over anything like sales, quantity, revenue, costs, transactions, or forecast, for example. It’s all the same technique. You just need to sub-in the correct core measure into the actual pattern or formula combination.
So let’s dive into it.
First, I’m going to set up a generic table, so we can look at the data itself. I’m going to bring in my core calculation, which in this case is Total Sales. Getting things into tables, to begin with is key.
Now I’m going to show you how to do this financial year total. I’ll go create a new measure and call this Sales FYTD (financial year to date).
I’m going to use the CALCULATE function, which changes the context of the calculation. So I still want to calculate the Total Sales, but only for a financial year to date. This enables me to select what my financial year is, and then always be accumulating the amounts up to the end of the financial year.
Then, I have this time intelligence function called DATESYTD (dates year to date), which returns a set of dates in the year up to the current date. If I don’t specify something specifically for my financial year, it’s literally going to do the entire calendar year. And so, I put my Date column in there.
And then I want to specify the actual financial year. So maybe my financial year ends in June, right? But, it could also be at the end of March. So in this example, I put 31st of the third as the end of my financial year.
I push enter and I’ll drag this measure into the table, and you’ll see here that it is working out the financial year results. Now, remember that this can make it a bit tricky. I selected here on the slicer a calendar year, which is 2023. And so we’re actually seeing March 31st of 2023 as the end of the 2023 financial year.
Sometimes it’s easier to look at this within visualizations. So I’ll create an area chart here and you can see that we come to the 31st of March 2023, which is the end of our financial year.
A better way to set this up is to have a financial year within the slicer. It makes more sense. In my Date table, it looks like there’s a financial year already embedded.
And so, I’ll go to Transform Data and work out the financial date that was embedded into this Date table. In the Advanced Editor, you can see that this is a Date table that enables you to specify the financial year, and here it started in July. So let’s just go with that.
So I’ll go back to the formula and change the financial year-end to the 30th of the sixth. This way, we can align the actual financial year of the Date table.
With that, you can see the results in the visualizations changed as well.
Then, I’m going to bring in my financial year (FY) in the slicer instead of my Year. You’ll see that if I select one of these, it’s going to give me just the cumulative total for that particular financial year.
I can also do a multi-select and the results in the visualization changes as well.
You can also utilize the Analyst Hub really effectively here. This is where you can format your calculations well. Just go to DAX Clean Up App.
Then you can paste the formatted formula back into your model like so.
Now, if you want to have the ultimate Date table, I highly recommend you go to the Enterprise DNA forum and into the M Code Showcase. Here you can find the Extended Date Table (Power Query M function). I myself use this Date table pretty much in every development that I do.
And as you can see, it has a lot of views already. It’s one of the most popular pages on our site.
Calculating a Power BI financial year to date is just this easy. You could also do quantitative financial year to date or anything else at all. You just branch off from a different core measure and utilize the same technique.
As soon as you become familiar with calculating time intelligence functions, you’ll discover how amazing Power BI is. All the best with working through this one.
Cheers!
Sam
Update the detailed information about Power Bi Reporting Templates Expanded – Power Bi Visualization Concepts on the Moimoishop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!