A finance user case
For financial statements, there is often a requirement to create a single measure that contains both integers and percentages. In order to achieve this, 3 specific challenges must be overcome, namely:
Percentages are non-additive and, therefore, should not be stored in the fact table. For instance, you cannot calculate the combined gross profit percentage for January and February from the individual January and February gross profit percentages
Formatting is unique to each individual financial statement line. For instance, Total Revenue in thousands or millions and gross profit percentage as a percentage
The solutions should integrate seamlessly with Excel. Excel formulas and pivot tables should be linked to the API to keep all Excel files and reports aligned
Solution
Rather than storing percentages in the fact table, they should be derived from the numerator (top of the fraction) and denominator (bottom of the fraction). By using the component parts, any percentage can then be calculated on-the-fly at any granularity. For instance, for January and February the sum of the Gross Profit (numerator) is is divided by the sum of Total Revenue (denominator) for that period.
Therefore, the initial solution (as seen in a previous article) is adapted with 2 additional columns (a numerator and denominator) in the P&L lines table:
For the numerator, the calculation:
Takes the figures in the numerator column (5)
Looks it up in the PL SK column
Performs the calculation (either sum or subtotal) as described in the previous article
The same process is applied to the denominator, and finally the numerator is divided by the denominator. This results in the following:
Formatting
This resolves the issue of calculating percentages, however the issue of formatting still exists; 0.6 should be displayed at 60%. Many solutions try to resolve this using FORMAT; this is not advised as it converts everything to text. This does not integrate well with Excel and becomes cumbersome to maintain. Instead, add the formatting string required for each individual line to a column in the P&L lines table:
Calculation group formatting strings are then used to apply the appropriate formatting for each line. This results in the following output:
Conclusion
This solution is easy to maintain and integrates seamlessly with excel. It's easy to maintain because any changes (i.e., to the numerator, denominator or formatting) are changed in a single location; the inputs to the model. This flows into all reports and Excel via pivot tables and formulas pointing to the API.
It further integrates with Excel because formatting strings are appropriately applied in Excel. Moreover, it futureproofs the solution when other calculations - such as Earnings Per Share (EPS) which requires a prefixed currency symbol (i.e., £ or $) - are introduced.
To learn more and build out your financial solution, join over 1,000 people who have taken the free course on Power BI P&L fundamentals