You are reading the article Financial Information Management Reports In Power Bi updated in December 2023 on the website Achiashop.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 January 2024 Financial Information Management Reports In Power Bi
In this Power BI Showcase, we’ll be focusing on financial information management reports for an enterprise involved in the global food distribution industry. You may watch the full video of this tutorial at the bottom of this blog.
The data set presented in the reports are common metrics received by an organization at the end of a financial year.
These financial metrics include Total Sales, Cost of Goods Sold, Gross Profits, Budgeted Sales, Variance to Budget, and Total Discounts.
This Showcase is broken down into three reports.
The first report page drills into the revenue and profitability of the business.
We can easily navigate around the key subsidiaries within the organization and see financial trends for a specific time period, product, or product category.
This allows us to build up the financial intelligence within the organization to optimize product allocation and sales team management.
The information in this report is also dynamic. If we want to look at details on the subsidiary in Indonesia, we can select the Total Sales Distribution by Regions chart.
By making this selection, all the other key metrics in this report automatically update.
We can also look at our biggest selling entity in Australia.
The chart in this report makes it easy to see how well our products have been selling in the Australian market.
This is a great way to identify which products are selling well and in what location these high Sales are coming from.
The next report page gives a breakdown of each product the company is selling globally.
We can see the different category breakdowns by product. There’s Total Sales, Variance to Budget, and Gross Profit Margins.
Gross Profit Margins are key in identifying why there’s a variance in Sales for different regions.
This helps in deciding where we want to potentially send more goods so we can obtain the highest Total Net Profit by the end of the financial year.
We can drill into a specific country, like Malaysia, and see which products are not performing well within that area.
This gives us a better understanding as to why these trends or numbers are occurring.
We can also multi-select countries and specific product categories. For instance, we can look at how our meat products are performing in Australia and New Zealand.
Lastly, we have a report on our channel and customer segments’ financial information.
In this example, we have four channels and two customer segments:
By clustering our financial information into these groups, we can easily identify which area we should place more focus on to make the most out of our Sales.
Let’s look at our Wholesale Channel:
With this, we can easily see our cumulative financial information throughout the year.
In this chart, we can see that there’s a big dip in our Variance to Budget between the months January and February.
We can proceed to investigate why this has happened and then start creating strategies to ensure that this won’t happen in the future.
The reports presented in this Showcase offer great insights on financial information management.
This is a great help for businesses that want to optimize their Sales and product allocation.
Power BI is a great tool to use in financial analysis because it makes it easy to drill down into specific parts of our data set.
All the best,
You're reading Financial Information Management Reports In Power Bi
I stumbled across a tool that is just so good and useful for some really common tasks in power BI when creating reports and visuals. It’s a color hex codes picker that you can use to easily get the colors for your Power BI reports. You may watch the full video of this tutorial at the bottom of this blog.
The tool is by a firm called Anny software and I want to give them a shoutout not only for making an awesome tool but for making it completely free. It’s got tremendous functionality and it solves some common problems and annoyances when dealing with hex codes and color themes in Power BI.
Let me show you some of the things that I’ve been using it for.
One of the common things that happens to me all the time is needing to figure out what the hex code color for something I’m using in my own report. For example, in this report, I would have to figure out what this blue color is. This is a PowerPoint background, so Power BI has no idea what that color is.
I would typically have to get a screenshot and then go into the effects tool on the eyedropper. But with this Just Color Picker, you don’t need to do any of that.
You just point to the blue and it shows you what the hex code is. And then from here, you can copy that value.
It’s really an easy way to build out a complete theme with this tool and dovetails perfectly with the Enterprise DNA Colour Theme Generator.
With the Color Theme Generator, you can go to Image to Colours, and then you can upload your own image here. I’ve uploaded an image here that I like, and I think it’s going to make a good theme.
And so the Image to Colours pulls out five colors from that theme. Then, I’ll use the color hex codes picker (Just Color Picker) to get the hex codes and then copy them.
I like this red from the lighthouse, so we can get that one as well. I also like the sand color from the promenade, so we can add that one to the list too. There’s a white from the waves that I also think will look good.
We can take all that. Then, I can take the blue base color that I like and from the sliders (HSV and RGB), we can take and create additional colors from that where we can change the saturation, etc. And we now have this dark blue-green color.
We can easily create a really nice color theme right away from this.
It has some built-in tools that are really handy. If you go to Tools and Color Wheels, it’ll show you for a given color what the complimentary colors are, and what the triad colors are.
You can choose more colors form here.
Then, you can go to the Tools again and there’s a function called Text.
This will show us the different combinations of background and texts. So clearly, you can see here in our example color combination that it doesn’t work at all.
Let’s take our text and change that to our promenade sand color and now it looks pretty good.
We can also change the background if we want to. We can just play around with the different colors in our theme to see if we need some additional complimentary colors.
Then, copy and paste this on a json file. We just need to get rid of the spaces and add quotes and pound signs. This is an example of what it should look like.
After that is saved, we can then pull it into Power BI.
Let me show you the final thing I do with this. This is something that I commonly do with my Power BI Challenge entries and reports. It’s modifying icons, particularly for navigation.
I’m using a service called Flaticon that has millions of really high-quality icons.
I’ve saved a bunch in a mini collection. These are ones that are frequently used. Let’s take this icon as an example. I use this as my standard for clear filters.
Let’s say we want to change that color from that greyish-black into something that matches the color theme that we just created. We’ll pop up the color hex codes picker, and in Flaticon, we can go to Edit Icon.
In the upper left corner, we can choose the color that we want to change. Then, we choose the color that we want from our color theme in the Just Color Picker. Copy the values of that color.
Paste the values or the hex code into the “new color” area. Format it correctly, and then we have the desired color in our icon.
Those are just three easy ways to use this color hex codes picker. It’s a great tool that you can use with your Power BI reports. I’m sure you’ll come up with many more ways to use this tool.
I hope this is helpful for you. Check out the links below for more relevant content.
Recently, I built and showed a Budgeting Analysis Dashboard in one of Enterprise DNA’s workshop. One feature of that Dashboard is the Cumulative Budget view. The webinar is linked here. You may watch the full video of this tutorial at the bottom of this blog.
Product Budgeting Analysis Dashboard
The dashboard itself is dynamic so I can change the time frame and select the products I want to track. This makes exploring the data extremely efficient if you’re comparing it against a benchmark.
Dynamic selection of time frame and products.
There was a seasonality aspect on my budget data and I needed to display it cumulatively. The visualization I created compares the BUDGET against SALES and SALES LAST YEAR. The dark blue line represents BUDGET and gives a good direction how the performance is against SALES.
In my view, using cumulative totals is the best way to evaluate trends. How your actual results compare versus your budget is ultimately what we want to look at.
We will discuss setting up cumulative totals in detail for this tutorial. We will discuss the formula and technique I used to do it for this dashboard.
First, we need to go to another page to set up the scenario and data table. This makes it easier to see what’s going on with the data itself. We then create a data table with DATE, the TOTAL SALES from the Key Measures and the BUDGET ALLOCATION from the Budget Measures.
The budget is set to allocate for every single day because the data context is by DATE. At the moment, the budget is not cumulative. We are going to use DAX formulas to make it so.
The formula looks complicated but if you work out how it’s set up, it will make sense. Variables are used in the formula – I will link the tutorial for the formula in detail.
Budget Allocation formula.
In your data, the budget can come in different granularities. It can be monthly like in the example above, it can also be yearly or weekly – this depends on how you define your data in the beginning.
Power BI allocates the budget based on how you set up your formulas.
The Product Budgets is even more complex because aside from the Amount, it also has the Product ID.
We can switch the DATE field into MONTH & YEAR instead and still get the correct breakdown because our formula is dynamically set up.
Using Date to group data.
Using Month & Year as the date field.
We’ll use bar charts to visualize this data and compare the daily performance to our budget allocation. This already gives us a good insight in itself – however, this is still not cumulative.
Next, we duplicate this chart and turn the duplicate into a table to see the actual values.
We’ve talked about Cumulative Sales many times before which follows this formula:
The Cumulative Budgets has a slightly different approach because we need to use complex DAX formulas.
The big difference when calculating Cumulative Budgets is that we can’t use the Budget Allocation by itself. It needs to pass through several DAX formulas to refine it.
If you notice on the right hand side of the SUMX formula we have the Budgets variable. What’s interesting here is we declared the Budgets variable inside the SUMMARIZE formula, DAX formulas can use a column that you virtually created as a reference right away. You will see it is similar with our other Cumulative formulas, except the SUMX portion.
To review, we SUMMARIZE the Budget Allocation at the same time, creating the Budgets variable. We then use SUMX on this Budgets variable to create the virtual table where we get the cumulative totals.
Now we add the Cumulative Budgets column to the table and we see that it adds the budgets cumulatively on all dates. This is a great way to represent seasonality in your data.
We then remove the columns we don’t need and change the table into a graph. This represents the data effectively in a cumulative way and shows the deviation better.
From the visualization perspective, you will identify trends better by using different elements together. I went through many other samples in the Advanced Budgeting Session. I’ll put a link below to the replay which is up on Youtube as well.
If you want to play around with this sample file, it is up on the Showcase page.
All the best
Power BI – Uses in Finance
A Microsoft-owned business analytics service that offers a wide range of data visualization and data warehousing services
Published April 27, 2023
Updated July 7, 2023What is Power BI?
The tagline for Power BI, “Bring Your Data to Life,” very clearly demonstrates the purpose of the Microsoft-owned business analytics tool. Power BI is an assortment of several data analytics-based services and systems that primarily focuses on visualizing business data and making it more interactive for organizations.
Created by Microsoft, Power BI is a cloud-based service provider offering data visualization and data warehouse services aimed at making data more interactive for the user. The application includes a wide range of data analytics services such as data preparation, custom visualization, data discovery, data warehousing, data reporting, interactive data sharing, data organization, and many others.
Owing to its substantial popularity, Power BI is available across different usage platforms. Power BI Desktop is used to access the analytics tool using a Windows-based desktop. It can also be accessed online using Power BI online SaaS, which is the application’s online software service. In addition, it can also be accessed using Android and iOS devices using the Power BI applications developed for the purpose.Key Highlights
Power BI is an assortment of several data analytics-based services and systems that primarily focuses on visualizing business data and making it more interactive for organizations.
Power BI is a cloud-based service provider offering data visualization and data warehouse services. The services include data preparation, custom visualization, data discovery, data warehousing, data reporting, interactive data sharing, data organization, and many others.
Owing to its substantial popularity, Power BI is available across several different platforms.How is Power BI used in finance? 1. Easy consolidation of large to very large data sets
Power BI is a very efficient tool for business data organization. There are generally different kinds of limitations regarding the volume, nature, and complexity of data, and its reporting and organization across various data management mediums. However, this application offers exceptional financial data management services, with absolutely no limitations on the reporting of financial data, no matter how large the company is or how complex its data.2. Excellent projections mechanisms
Power BI offers exceptional data projection systems. Financial projections are an integral part of a business’ operations, and several vital decisions are drawn essentially from financial projections. Hence, it is an integral part of any organization’s data management processes.
The application offers a data projections function called “what-if parameters” that create interactive data projections and are very efficient for comparison. It is a vital tool to draw up projection statements of any number and kind of assumptions.3. Data trends and patterns
Power BI comes with a built-in time intelligence feature. It also provides the ability to arrange data in accordance with different data dimensions and parameters. Using such features, it is very easy to spot data trends or data patterns over several years or across the market competition. They are very useful in drawing important conclusions about business operations and making important financial decisions about profitability, budgeting, business expenses, etc.4. Quick Insights feature
Power BI runs on powerful data analysis algorithms, which fuels the efficient working of the Quick Insights feature of the software. The tool provides data implications and draws various facts and conclusions from the dataset provided by the user. It is a very useful tool for a financial planner who would want some insight or draw conclusions from the financial statements for the year, and so on.5. Power View
Power View is an essential feature of Power BI that allows generating interactive charts, graphs, and data maps. It helps generate visually interactive charts and graphs from the financial dataset and consolidating that information to draw conclusions and make important financial decisions.6. Collaboration
Power BI is a collaborative platform, i.e., it is easily shareable and accessible across different users, while, at the same time, offering high security and protective measures. Hence, the finance department of the business or financial management teams of projects can collaborate. They can work together in sharing financial datasets, publishing financial reports and dashboards, exchanging vital data conclusions, and working collaboratively and efficiently.Conclusion
The aforementioned uses of Power BI serve the purpose of financial data management and analysis exceptionally well. However, Power BI is not limited to just such uses; it offers a wide array of services for data management. It is considered an integral tool of financial management for large businesses and corporations, where financial data is in large numbers and is much higher in complexity.
To learn more about the dashboarding tool, check out CFI’s Power BI Fundamentals course! Learn more about telling meaningful stories with data, organizing and manipulating large and complex datasets, and creating powerful dashboards.Related Readings
In this tutorial, I’ll discuss how to use the ROUND function in Power BI when it comes to conditional formatting. Excel users might be familiar with how this function works in Excel, and we actually use it in a similar way in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
I recently encountered a problem with conditional formatting, and I’ll show you how I solved it using this function. I needed to highlight a number in a certain column if the results from the other 2 columns didn’t match. After talking to one of our Enterprise DNA experts, I figured out that I just needed to use a DAX function called ROUND.
The ROUND function is used to round a number to the specified number of digits. You can check the Microsoft documentation for this function here.
The number term refers to the number that you want to round. In my case, this is the measure. On the other hand, the num_digits represent the number of digits from the decimal point that you want to round.
You can also check these considerations when using the ROUND function.
Let’s discuss how I used this DAX function in a calculation and conditional highlighting that I did for a client.
Here is the situation that I had when I did the Margin Target calculation and conditional highlighting for my client. I broke down this table by job. I also added a Job Count column so we could see the total amount of jobs.
In this scenario, I used a measure that I named as Margin Target Test to get the results for the Info Page Margin column.
The Info Page Margin column is a margin calculation. Typically, the margin is in a number form. So, I used this measure to divide the margin to 100 in order to get the percentage.
In the original measure, I was trying to highlight the number in the Info Page Margin column to orange, if it doesn’t match the actual margin which is the number in the Margin% v2 column.
To show you that, here’s a tab that I labeled as incorrect. This will show you the highlighted incorrect values.
As you can see, the margin from the Info Page Margin column is 37.5%. Then, the numbers in the Margin% v2 and TESTING columns matched. In that case, the 37.5% shouldn’t be highlighted. This table shows an incorrect output because of the original way that I’ve set up the measure.
Here’s the incorrect measure that I used for the previous scenario.
In this measure, I created a variable called MarginNoGood. This variable contains a condition where if the result from the TESTING column doesn’t match the number from the Margin% v2 column, the value will be set to 1. If not, the value will be set to 0.
Then, I created the CompletedMargin variable. I used this to calculate the number of jobs that were under the “Job Completed” status and those that resulted in 0 from the MarginNoGood calculation.
After that, I used the RETURN keyword wherein I could get a 6 or a 0 that I can use to conditionally highlight the background of the number under the Info Page Margin column.
Then, there will be different options here. In this example, I used the Rules options.
From there, I set a rule where if the value is 6, that result from the Info Page Margin column should be highlighted with an orange background.
With the previously mentioned calculation and conditional formatting setup, the numbers under the Info Page Margin column were highlighted incorrectly. As you can see, the numbers under Margin% v2 and TESTING columns matched but the numbers in the Info Page Margin column were still highlighted.
So, I had to use the ROUND function for it to work correctly.
I created another measure that I named Job Info Margin. The formula that I used in this measure is almost similar to the previous one. However, I used the ROUND function in this formula. I also used 3 as my num_digits. That means I want to round it to 3 decimal places.
By doing that, all the numbers in the Info Page Margin column are now highlighted in orange. That’s because the numbers in the Margin% v2 and TESTING column didn’t match.
As I scroll down the table, I can see that there are rows where the Margin%v2 and TESTING column matched. Therefore, it didn’t highlight the numbers under the Info Page Margin.
And that is the correct output that I need. Hence, with the help of the ROUND function, my conditional highlighting is now working correctly.
On a final note, the ROUND function in Power BI is definitely valuable when it comes to conditional formatting. For those who are familiar with Excel, you’ve probably had some experience at some point using the ROUND function. But here in DAX, if you ever encounter an instance where you’re stuck with analyzing why two numbers or percentages don’t match, try using this function.
I hope this helps you in your future DAX endeavors.
Check out the links below for more examples and related content.
In this tutorial, you’ll learn about the different Power BI compression techniques in DAX Studio that help optimize your report.
After data is loaded segment by segment by the Analysis Services in Power BI Power Pivot and SSAS, two events occur. The first one is that they try to use different encoding methods to compress columns to reduce the overall RAM size. The second one is that they try to fund out the best sort order that places repeating values together. This method also increases compression and in turn, reduces the pressure on the memory.
There are different compression techniques used by Analysis Services. This tutorial covers three methods, in particular, namely, Value Encoding, Run Length Encoding, and Dictionary Encoding. In the last section of this tutorial, it’ll cover how to sort order works in Analysis Services.
The first one is called Value Encoding.
Value Encoding seeks out a mathematical relationship between each value in a column to reduce memory. Here’s an example in Microsoft Excel:
This column requires 16,384 bits in order to store the values.
To compute the bits required, first use the MAX() function in Excel to get the highest value in the columns. In this case, it’s 9144. Then, use the POWER() function to calculate the bits required. Use the argument POWER(2, X) where X is any positive value that will return an answer that’s greater than the MAX value. X, in this case, also represents the bits required. So for this example, the value of X is 14 which results in 16,384. Therefore, the column requires 14 bits of storage.
To reduce the required bits using Value Encoding, VertiPaq seeks out the MIN value in the column and subtracts it from each value. In this case, the MIN value in the column is 9003. If you subtract this from the column, it’ll return these values:
Using the same functions and arguments, you can see that for the new column, the MAX value is 141. And using 8 as the value of X results in 256. Therefore, the new column only requires 8 bits.
You can see how compressed the second is compared to the first column.
Once the data is compressed and you try to query the new column, the Storage Engine or Vertipaq scans this column. They won’t simply return the new values of the column. Instead, they add the subtracted value before returning the result back to the user.
However, Value Encoding only works on columns containing integers or values with fixed decimal numbers.
The second encoding method is called Run Length Encoding.
Run Length Encoding creates a data structure that contains the distinct value, a Start column, and a Count column.
Let’s have an example:
In this case, it identifies that one Red value is available in the first row. It then finds out that the Black value starts at the second row and is available for the next four cells. It proceeds to the third value, Blue, which starts at the sixth row and is available for the next three rows. And this goes on until it reaches the last value in the column.
So instead of storing the entire column, it creates a data structure that only contains information about where a particular value starts and where it ends, and how many duplicates it has.
For columns with the same structure, data can be further compressed by arranging the values in either ascending or descending order.
With this properly sorted column, you can see that the Run Length Encoding method now returns a data structure with one row less.
So if you’re dealing with many distinct values, it’s recommended to sort the column in the most optimal way possible. This will give you a data structure with lesser rows which in turn occupies lesser RAM.
Run Length Encoding can’t be applied to primary keys because primary key columns only contain unique values. So instead of storing one row for each value, it’ll store the column as it is.
The third encoding method is called Dictionary Encoding.
Dictionary Encoding creates a dictionary-like structure that contains the distinct value of a column. It also assigns an index to that unique value.
Using the previous example, let’s look at how Dictionary Encoding works. In this case, the values Red, Black, and Blue are assigned an index of 0, 1, and 2, respectively.
It then creates a data structure similar to that of Run Length Encoding. However, instead of storing the actual values, Dictionary Encoding stores the assigned index of each value.
This further reduces the RAM consumed because numbers take up lesser space than string values.
Dictionary Encoding also makes the tabular data type independent. That is, regardless if you have a column that can be stored in different data types, it won’t matter since the data structure will only store the index value.
However, even if it’s independent, the data type will still have an effect on the size of the dictionary. Depending on the data type you choose to save the column in, the dictionary (or data structure) size will fluctuate. But the size of the column itself will remain the same.
So depending on what data type you’ll choose, once Dictionary Encoding is applied on the column, Run Length Encoding can be applied afterward.
In this case, Analysis Services will create two data structures. It’ll first create a dictionary and then apply Run Length Encoding on it to further increase the compression of the column.
For the last part of this tutorial, let’s discuss how Analysis Services decides on the most optimal manner to sort data.
As an example, let’s look at a column containing Red, Blue, Black, Green, and Pink values. The numbers 1 to 5 have also been assigned to them. This acts as the dictionary of our column.
Now, fill an entire column in Excel with these values. Use this argument to generate a column containing these values at random.
Next, copy the entire column and paste it as a Value.
To reduce the amount of RAM consumed, you can sort the column from A to Z. If you check the size again, you can see that it’s been reduced to 12.5 MB.
The 1.9 MB reduction may not seem much. This is because the example used a single column in Excel to demonstrate. Excel is only limited to 1 million rows. However, in Power BI, your data can contain billions of rows and columns. The reduction in space used grow exponentially.
Once your data is sorted in the most optimal manner, Analysis Services applies either of the three compression techniques depending on the data type.
Doing so increases the compression of your data which greatly reduces the amount of memory consumed in your device. This makes your report more optimal making it easier to run and load.
Enterprise DNA Experts
Update the detailed information about Financial Information Management Reports In Power Bi on the Achiashop.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!