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 start by adding the fields and measures that you need for the visual in this case, we are simply creating a report, not writing back to aimplan this means we do not need to add fields to key fields , which are the key fields that are being stored to the table, nor do we need to add aimplan user key , which is the field that tells who made the input we will get to these fields in exercise 2 2 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 row sections 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) 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 column 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 start by having sum of actualcost , click on the three dots after actualcost and +add new expression; % divided by click on sum of to change that into static value instead click on static value and enter the value 1000 (expressions needs numeric scales to be done within the calculation) click on the three dots next to sum of actualcost and choose group click on the three dots next to sum of actualcost again and choose minus change sum of actualcost to sum of lastyearcost 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 format columns 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