Allocation and Visualization of Budgets with Seasonality | Power Query and DAX
In this session, we will look at a common budget allocation problem that involves seasonality. Seasonality has a major influence on many businesses. The ability to factor in seasonality in budgets and forecasts could help to create clear and meaningful comparisons with actuals. Allocating a high-level budget to lower levels of granularity, on the other hand, can be difficult. I'll demonstrate how to split seasonal budgets utilizing Power Query. This will allow us to compare the budget on a quarterly, monthly, and even daily basis. At the end, I'll demonstrate how to use dynamic DAX calculations for effective budget-to-actual comparisons.Income Statement Analysis: Part 1 of 5
In this 5 part series, we walk through the steps required to build out the first page of the Income Statement Report here. The topics covered in these walkthroughs include: - Part 1: Connecting to Data (Folder Containing CSVs), Importing into Power BI - Part 2: Creating the base measures and an Income Statement Matrix visual - Part 3: Adding advanced measures e.g. based on the period selection (covering use of disconnected parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formatsIncome Statement Analysis: Part 3 of 5: Format the page and add Dynamic Measure
In the previous posts (part 1 and part 2),, we set up a matrix visual which shows our total actuals, Prior Year Actuals and Variance to Prior Year for a given year/month (based on the slicer selection). In this section we want to take this a step further and choose to show either Monthly figures, or alternatively Quarter to Date or Year to Date for the selected year and month. We will also apply a background and theme to the report and will apply conditional formatting to the income statement matrix, dynamically calculating whether an increase in the number is good or bad (i.e. whether it is a revenue/profit or expense number). ________________ In this 5 part series, walk through the steps required to build out the first page of the Income Statement Report here. The topics covered in these walkthroughs include: - Part 1: Connecting to Data (Folder Containing CSVs), Importing into Power BI - Part 2: Creating the base measures and an Income Statement Matrix visual - Part 3: Adding advanced measures e.g. based on the period selection (covering use of disconnected parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formats ____________Income Statement Analysis: Part 2 of 5: Create Simple Matrix view with Actuals
In part 1 of this blog series, we connected to the folder containing the Monthly CSVs and the Reference Data Excel file and brought the data into Power BI. In this section we will create an initial set of calculations and show the results in a pivot-table style Matrix visual. ________________ In this 5 part series, walk through the steps required to build out the first page of the Income Statement Report here. The topics covered in these walkthroughs include: - Part 1: Connecting to Data (Folder Containing CSVs), Importing into Power BI - Part 2: Creating the base measures and an Income Statement Matrix visual - Part 3: Adding advanced measures e.g. based on the period selection (covering use of disconnected parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formats ____________
Help with Dax to calculate at rows level!!
Hi, I'm trying to calculate price impact base on fact sales data: Sales data contains sales information by invoices date, customer number and product. My price impact formular is : (Average selling price MTD - Average selling price same month last year)* Qty current month. The problem is the sub-category does not add up from rows level calculation. below is my dax: This is the current issue I have from sample
Simple Cost Ratio from GL Data
Hello all, pretty frustrated with what I feel should be a simple task I have a simple set of GL data, as seen below, where my amount is in one column, and my accounts are rows. I then do a very simple model where I have a GL Account class lookup table, which assigns the class "revenue" and "cost" to each account. I then just want to have a simple cost ratio at the account level, of cost/total revenue. the table should look as such below, but nothing returns at the account level for a margin. even the revenue accounts are wrong, as Revenue Reg should be 60% of total revenue, , and Revenue Spec should be 40% My measures are Value = SUM(data_table[Amount]) Total Revenue = CALCULATE([Value],gl_class[Class]="Revenue") Cost Margin = DIVIDE([Value],[Total Revenue]) any help would be greatly appreciatedIncome Statement Analysis: Part 5 of 5: Add IS Tables and Bookmarks
Congratulations! You’ve made it to the final step of completing the Income Statement page. We already have a pivot table style matrix with conditional formatting, Dynamic card visuals showing our key numbers (with a comparison to prior year) and a waterfall chart showing breakdowns for the month/quarter/year to date by Organisational unit. In this section we will build different styles of the Income Statement. Rather than just having the expandable/collapsible headings, we will now have sub-totals such as Gross Margin and Operating Profit to appear in a more traditional fixed format style of reporting: We will also build a third Income Statement which has Organisational Unit across the columns of the matrix: As shown in the above screenshot, we will have buttons for the user to switch between the Income Statement formats, making use of the buttons functionality in Power BI. ________________ In this 5 part series, we walk through the steps required to build out the first page of the Income Statement Report here. The topics covered in these walkthroughs include: - Part 1: Connecting to Data (Folder Containing CSVs), Importing into Power BI - Part 2: Creating the base measures and an Income Statement Matrix visual - Part 3: Adding advanced measures e.g. based on the period selection (covering use of disconnected parameter tables) and variances - Part 4: Adding KPI cards and Waterfall charts with dynamic axis/Arrow performance indicators and measures to identify good/bad performance for advanced conditional formatting - Part 5: Creation of a fixed format Income Statement (With running total measures) and creation of bookmarks to switch between the income statement formats ______________________________Calculate opening balance together with transaction based data into one visual
Hello. I hope you can help me out I have to show both open and closing balances based on a lot of transactions from one dataset. I have to show opening balance up top and closing balance in the buttom of my matrix visual and in between is the kind of transaction which have adjusted the opening balance for the selected month. My data comes in the following diminsions: Date, amount, Transaction group. My problems is, how do i get the opening balance to show closing balance of last month for this month. Currently it is look like this: (Sorry for the red markings but the groups a confidencial) But i need it to go like this: I have gotten the first opening balance by creating a transaction group called opening balance and manuelle putting into my dataset. This is not a year to date visual. I have to see the impact each transaction group have on the "current" opening balance pr. month. Looking forward to hear from you.Gathering Stock Market Information Using APIs
If you want all your senior team looking at the same stock market information, creating a single report and pulling up-to-date information using APIs is a great way to achieve this. This blog gives you an end-to-end explanation of how to achieve this along with a Power BI Desktop file as an example.
