You are reading the article Showcasing Budgeting 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 Showcasing Budgeting In Power Bi
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
Sam
You're reading Showcasing Budgeting In Power Bi
Power Bi – Uses In Finance
Power BI – Uses in Finance
A Microsoft-owned business analytics service that offers a wide range of data visualization and data warehousing services
Written by
CFI Team
Published April 27, 2023
Updated July 7, 2023
What 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 setsPower 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 mechanismsPower 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 patternsPower 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 featurePower 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 ViewPower 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. CollaborationPower 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.
ConclusionThe 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 ReadingsFinancial 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,
Sam
Round Function In Power Bi – Conditional Formatting
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.
Thank you!
Jarrett
Power Bi Compression Techniques In Dax Studio
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
Power Bi Vs Tableau Vs Qlik
Difference Between Power BI vs Tableau vs Qlik
Power Bi is a Business Intelligence tool we can upload data and publish data throughout our companies. Business Intelligence response to any query and improves decision making. Adding power to the business for good visualization of data. Another feature of Power BI Is Quick Insights in which we can search a dataset for interesting patterns and provides a list of charts for a better understanding of data. It uses artificial intelligence and data mining to analyze the data. Qlik is also a Business Intelligence and data visualization tool.
Start Your Free Data Science Course
It is an end – to end ETL solution yielding good customer service. With qlik, we can create a flexible end-user interface, make good presentations based on the data, creates dynamic graphical charts and tables, perform statistical analysis, builds own expert systems. Qlik can be used with the virtual database. It is a window-based tool that requires the following components: Qlik Server, Qlik Publisher.
Features of Power BI
Rich Graphical visualizations from complex BI data.
Ad-hoc Reporting
Good Navigation Pane
Includes a dataset with customizable dashboards.
Features of Qlik
With the use of a hybrid approach, the user can associate data stored within big sources are stored within the Qlik view in-memory dataset.
With the help of direct discovery, they allow users to perform Business discovery and visual analysis.
Mobile-ready, Roles, and Permissions
Interact with dynamic apps, Dashboards.
Features of Tableau
They have a good drag and drop.
For data sharing, they use Tableau Public.
They implement interactive data visualizations on the web.
Their performance is strong and reliable and operates on huge data.
They are mobile-friendly and supports full online versions.
Head To Head Comparison Between Power BI and Tableau and Qlik(Infographics)Below is the top 9 difference between Power BI vs Tableau vs Qlik
Key Differences Between Power BI and Tableau and QlikThese are popular choices in the market; let us discuss some of the major difference:
Qlik can be immediately accessed by multiple users. Qlik is faster than Tableau. Power bI connect to any data source they do not require ETL.
The documents are stored in. the qvw format we can access these documents via Qlik Views proprietary communication protocol and stored in Windows OS, and all the events are taken in Qlik Server, they are responsible for Client-Server Power BI has three types of files excel(.xls), power BI desktop(.pbix), (.csv). Tableau extract files can have (.tde) file extensions.
Qlik structure is not well managed whereas Tableau structure is managed by well user guide.
Qlik works as a standalone technique. Publishing the data to the outside world are managed by QlikView Publisher. Power BI is available only on the SAAS model whereas Tableau has cloud and on-premises option. Power BI desktop version is free.
Data modeling facilities are increased using POwerBI. In Qlik Data insights are generated rapidly.
Tableau and Power BI is user-friendly. Qlik has high customizable patterns.
Qlik and tableau work for statistical analysis. Power BI does not have this capability.
Power BI vs Tableau vs Qlik Comparison TableBelow is the 9 topmost comparison
The basis of comparison Power BI Tableau Qlik
Performance It Lacks behind on data visualizations. They are more User-friendly because Non- technical users can work with this tool. They use cubic Technique Qlik needs a developer to work with reports and dashboards. They take all types of datasets.
They have good visualizations.
User Interface
Dashboards are the key feature of PowerBI when has a good User interface to publish the report.
User Interface is better
User Interface is quite down when compared with Tableau.
Ease of learning
User-friendly- Knowledge of Excel is enough
They do not require any technical or programming skills to work with.
Easy to learn with Data science background.
Supportive requirements
Power BI has Power BI desktop, Gateway
They work with front-end tool such as R.
Qlik consists of both front-end (Qlik Developer) and back-end (Qlik Publisher)
Version
The desktop version is free, Power BI Pro is pay per month. Tableau Reader is a free version. Tableau Server is a licensed one. Qlik Personal Edition is a free version of Qlik and runs without a license Key.
Cost- Effectiveness Less expensive. Qlik website has two editions. Personal version is free, Enterprise version can be used contact with the team.
Online Analytical Programming They connect to OLAP cubes via SQL servers for multidimensional analysis. Tableau can connect to OLAP taking out the cube measures at the deepest level. Access to OLAP provides encapsulated data views.
Speed They have smart recovery Speed depends on RAM and data sets. They have better Speed since they store the data in the server RAM (In-Memory Storage)
Advantage Power BI are inexpensive and have scalability for larger projects. They are top ranked in intelligence visualizations. They provide wide range deep analytics and they have good customer satisfaction
ratings.
Conclusion Recommended ArticlesThis has a been a guide to the top difference between Power BI vs Tableau vs Qlik. Here we also discuss the key differences with infographics, and comparison table. You may also have a look at the following articles to learn more.
Update the detailed information about Showcasing Budgeting 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!