Showing Actuals & Plan values on Rows

In some scenarios, you may want to display values on rows. Both planning values and actuals. It is possible to achieve, but not trivial, since aimplan only creates rows for unique combinations in its hierarchy.

Consider this table:

Aimplan obviously cannot group on planning or actual values. For it to create a row per value, we need something for it to group on. We need to pivot the table, so it is groupable.

To achieve this, we will need a table that consists of our values. We'll create one by opening our report in Power BI desktop and click on "Enter Data" in the Home Ribbon.

Table Name: AdditionalMeasures

Once we have that table, to make sure that everything filters correctly, we will create a relationship between it and our "DimMeasure" table.

This table will then enable us to create a switching measure that allows us to produce unique rows for each measure. The measure will look something like this:

In this measure, we are getting our planning values through the "Plan Sales Quantity" measure, and our actuals through our sales measures.

If this table is used in a table, along with our AdditionalMeasures[MeasureName] you should see something like this:

And if MeasureName is grouped on rows, you will successfully get your actuals on rows above or below your planning values.

Then by dragging the "MeasureKey" from the AdditionalMeasures table, we can then also enable input on this value. However, to make sure we do not accidentally try to write to the non-editable measures, we will create a normal measure lock.

Since we've already connected our AdditionalMeasures to the DimMeasures, we can look up if the measure is locked by having a DAX measure like this:

Finally, you may want to re-order the measures you've added. This can be done simply by editing the Additional Measures table that we created and adding an "Order" column.

Then, just like any other dimension, we can sort it in Aimplan through the Sort By option.