The increasing demand for flexible financial reporting is well known within the Dynamics 365 for Finance and Operations user-base. To match these requirements a lot of companies turn to Power BI because of it's fluid user experience and extensive capabilities.
Companies using Dynamics 365 for Finance and Operations or earlier AX versions already experience the great capabilities and flexibility of the financial structure, using financial dimension structures and chart of accounts.
To improve the reporting capabilities, the financial structures can be enhanced by introducing dimension hierarchies, dimension totals, main account categories, additional consolidation accounts or custom fields. When using main account categories to improve financial reporting I suggest checking out Alex Calimen’s blog post.
In this post I will demonstrate how to model dimension totals in PowerBI to allow for additional slicing and dicing of financial data. To improve this functionality in Dynamics for Operations up-vote my Dynamics improvements idea.
Setup dimension totals
Dimension totals are designed to allow for roll-up reporting based on the dimension values and work seamlessly in Financial reporter / Management reporter. It requires a little bit of modelling in PowerBI to allow dimension totals to work. In this post the dimension totals function as a dimension attribute. In order to allow for multiple dimension attributes the dimension totals need to be associated with a dimension group.
An example of a two-dimensional roll-up would be:
Where the dimension total is setup like:
Because there are two different group dimensions (Subtotal and Group Total) the individual dimension values can have two attributes. With the above input the following dimension table including attributes can be built using the query editor in PowerBI.
Export data to PowerBI
Before the data model can be updated, the data has to be made available in PowerBI.
For financial dimensions you want to use the Financial dimensions value and Financial dimension value totaling entity.
In case you also want to use additional main account attributes; use the main account and additional consolidation accounts entity.
PowerBI query
Start by merging the financial dimension values with the financial dimension totals value table via a the financialdimensionvalue and financialdimension, in case the same dimension value is used across different dimensions.
Expand the join so the dimension value is included in the table. Then filter the financial dimension list for dimension which are not used for dimension totals (IsTotal = No). As a result the table should show all dimension values and their relevant dimension totals.
Remove, rename and reorder the table as desired. But ensure the financialdimension, dimensionvalue, goupdimension and dimensiontotal columns remain within the table. You will need these columns later on.
Now that the dimension value and dimension totals are combined in one table, the dimension total needs to be pivoted, to create the attribute columns.
Select the groupdimension column and pivot the column, ensure the dimension totals are used as values and don’t forget to use the don’t aggregate function.
The Pivot function will return an error when a dimension value is included in the same group dimension and is used in more than one dimension total value. Use the replace error function in PowerBI to alert users of this problem.
Now the dimension table should look something like this:
The table includes the financial dimension code and two dimension attributes. To add the description of the attribute codes use the merge function as done before.
Use the same principles to create additional main account attributes using the main account and consolidation group and accounts tables.
I have included a copy of the PowerBI query I used to add two dimension attributes to a 'pro.duct' dimension.
That’s it for now...
let
Source = Table.NestedJoin(#"Financial dimension values",{"FINANCIALDIMENSION", "DIMENSIONVALUE"},#"Financial dimension value totaling",{"FINANCIALDIMENSION", "DIMENSIONVALUE"},"Financial dimension value totaling",JoinKind.LeftOuter),
#"Expanded Financial dimension value totaling" = Table.ExpandTableColumn(Source, "Financial dimension value totaling", {"DIMENSIONTOTAL"}, {"Financial dimension value totaling.DIMENSIONTOTAL"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Financial dimension value totaling", each [ISTOTAL] = "No"),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"FINANCIALDIMENSION", "Financial dimension value totaling.DIMENSIONTOTAL"},#"Financial dimension values",{"FINANCIALDIMENSION", "DIMENSIONVALUE"},"Financial dimension values",JoinKind.LeftOuter),
#"Expanded Financial dimension values" = Table.ExpandTableColumn(#"Merged Queries", "Financial dimension values", {"GROUPDIMENSION"}, {"Financial dimension values.GROUPDIMENSION"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Financial dimension values",{"FINANCIALDIMENSION", "DIMENSIONVALUE", "DESCRIPTION", "GROUPDIMENSION", "ISTOTAL", "Financial dimension values.GROUPDIMENSION", "Financial dimension value totaling.DIMENSIONTOTAL"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,"",Replacer.ReplaceValue,{"Financial dimension values.GROUPDIMENSION", "Financial dimension value totaling.DIMENSIONTOTAL"}),
#"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each ([FINANCIALDIMENSION] = "Product")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[#"Financial dimension values.GROUPDIMENSION"]), "Financial dimension values.GROUPDIMENSION", "Financial dimension value totaling.DIMENSIONTOTAL"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"", "GROUPDIMENSION", "ISTOTAL"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Removed Columns", {{"Prod Group", "Error: duplicate values"}, {"Prod 3PL", "Error: duplicate values"}}),
#"Merged Queries1" = Table.NestedJoin(#"Replaced Errors",{"FINANCIALDIMENSION", "Prod Group"},#"Financial dimension values",{"FINANCIALDIMENSION", "DIMENSIONVALUE"},"Financial dimension values",JoinKind.LeftOuter),
#"Expanded Financial dimension values1" = Table.ExpandTableColumn(#"Merged Queries1", "Financial dimension values", {"DESCRIPTION"}, {"Financial dimension values.DESCRIPTION"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Financial dimension values1",{"FINANCIALDIMENSION", "Prod 3PL"},#"Financial dimension values",{"FINANCIALDIMENSION", "DIMENSIONVALUE"},"Financial dimension values",JoinKind.LeftOuter),
#"Expanded Financial dimension values2" = Table.ExpandTableColumn(#"Merged Queries2", "Financial dimension values", {"DESCRIPTION"}, {"Financial dimension values.DESCRIPTION.1"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Expanded Financial dimension values2",{"FINANCIALDIMENSION", "DIMENSIONVALUE", "DESCRIPTION", "Prod Group", "Financial dimension values.DESCRIPTION", "Prod 3PL", "Financial dimension values.DESCRIPTION.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns1",{{"Financial dimension values.DESCRIPTION", "Product group description"}, {"Financial dimension values.DESCRIPTION.1", "3PL description"}, {"DESCRIPTION", "Description"}, {"DIMENSIONVALUE", "Product"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"FINANCIALDIMENSION"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Product", Order.Ascending}})
in
#"Sorted Rows"
Comments