2.1 Planning & Reporting Table

The Aimplan Planning & Reporting Visual is the original Aimplan Custom Visual and have extensive features and customizable choices. For a step-by-step guide on how to set up the Planning & Reporting Visual, take a look here.

In the Aimplan Training, the base setup within the Portal, as well as all relationships and measures in the .pbix-file, are already prepared for you, meaning that your first exercise is to set up a report using the Aimplan Planning & Reporting Visual.

C

For this exercise, we want to create a report with a layered account hierarcy, displaying costs on a full year basis for the chosen year and the previous year, as well as the change between the years, separated by the cost types Supplies and Other Costs. That would look like this:

First, you need to add normal Power BI slicers to filter out CostCenter and Year. Add them to the top of you report as in the picture above and choose single select for Year.

Add the Fields listed below:

If you click on the three dots in the top right corner of the visual and click Edit, you will be able to set up your visual.

Start by creating a row section by clicking on + New Row Section and add the fields SubAccountGroupName and AccountName. You can alter the apperance of the section and row section to your liking, for example by changing the Group Header Format and Total Row Format for the SubAccountGroupName to bold. By clicking on Preview you can see what your report currently look like.

Since we want to separate costs by Supplies and Other Costs, we will start by filtering out the accounts we want to keep in the first row section. You do this by clicking on the Filter under your fields and selecting the SubAccountGroupName to be one of Consumable equip. & consumable matl. or "Office/printed material costs.

You now have one row section, and we want to add a second one. To do this, we again enter Edit mode. Click on + New Row Section.

You will now get a question where you should either enter a Token from the portal (see active Token for Training here: https://docs.aimplan.com/aimplan-training) or use your Microsoft Entra ID to identify yourself and choose the instance you want to work with, in this case Aimplan Training.

Once you have entered the Token or used you Microsoft Entra ID and chosen your instance, you can add more rows and comment sections as well as enable the write-back function, which we will go through in the next chapter.

To create our next row section, the easiest way would be to duplicate the section we have already created and simply rename the new section and change the filters from is one of to is not one of.



In our second row section, we now have all the accounts we filtered out in the first row section, excluding the once we kept in the first section. We then only miss a total row that generates the total amount for all accounts.

We create that by simply creating a new row section, naming it Total, and format the section as we want it to look.



Your report should now look like this, feel free to alter the appearance of your report, for examble by adding spacers between the sections.



You choose which measures to display in the column sections. You create a column section by clicking on +New Column Section. For this Training, and add the values LastYearCost and ActualCost then add an expression to calculate the difference between last year and this year, called Change.

To create the calculation for Change you follow these steps in Field:

The Expression is now done and should represent the difference between last years cost and current years cost, in thousands.

To tidy up our report, we want the naming of the measures ActualCost and LastYearCost to be dynamic in relation to what year we are currently looking at, chosen in the Year slicer.

To alter the names, we go to our value under Edit and your column section and click on the toggle fx next to Alias. For your LastYearCost measure, you alter your expression to Maximum of;Year, add a "minus" static value; 1 . You do this to generate the value of last year, that is, this year minus one year. If you have selected year 2024, last year should be shown as 2023.



To get a cohesive feel, we want to style the columns for both values so that they have a set width. We do this by clicking on each value and open up Column Styling and set the column width to 80.

We also want the numbers to show in thousands (already done for Change), without decimal numbers and with a thousand separator. To do this, we click on our value and open up Number Format where you can alter all of these settings.

You have now created the intended report and it shoud look something like this: