Dynamic KPI and P&L Con­fig­ur­a­tion in Power BI: A Flex­ible, Con­fig­ur­a­tion-Driven Approach

What if busi­ness users could change their key per­form­ance indic­ator (KPI) defin­i­tions and Profit and Loss (P&L) struc­tures in Power BI without chan­ging the under­ly­ing data model or backend implementation?

This ques­tion inspired our pro­ject: build­ing a flex­ible report­ing solu­tion that sep­ar­ates busi­ness defin­i­tions from tech­nical implementation.

In many tra­di­tional report­ing solu­tions, developers embed KPI cal­cu­la­tions, account map­pings, and P&L hier­arch­ies in backend logic or the semantic model. As busi­ness require­ments evolve, even rel­at­ively small changes can require devel­op­ment effort, test­ing, and deploy­ment. This cre­ates par­tic­u­lar chal­lenges when dif­fer­ent depart­ments or users need dif­fer­ent views of the same fin­an­cial data.

Our approach makes these defin­i­tions con­fig­ur­able. By com­bin­ing Dat­ab­ricks for data pro­cessing with Power BI for report­ing and visu­al­iz­a­tion, we developed a solu­tion that lets users define their own KPIs, cal­cu­la­tions, and P&L struc­tures while keep­ing the under­ly­ing tech­nical imple­ment­a­tion stable.

In this art­icle, we explore the archi­tec­ture behind the solu­tion, explain how con­fig­ur­a­tion drives the report­ing exper­i­ence, and show how mul­tiple con­fig­ur­a­tions can coex­ist within the same framework.

Estim­ated read­ing time: 7 minutes

1. The Core Idea: Sep­ar­ate Busi­ness Logic from Tech­nical Implementation

The main design prin­ciple was simple: busi­ness defin­i­tions should come from con­fig­ur­a­tion rather than hard­coded logic.

Instead of embed­ding every account map­ping, KPI for­mula, and hier­archy dir­ectly into the pro­cessing logic or Power BI report, we moved these defin­i­tions into con­fig­ur­a­tion tables.

Users can configure:

• Which accounts con­trib­ute to each KPI.
• How the solu­tion cal­cu­lates P&L ele­ments, includ­ing sums and ratios.
• How KPIs and P&L ele­ments fit into a hierarchy.
• Which labels and sort orders appear in the report.
• How many hier­archy levels the report dis­plays, up to six.

For example, two busi­ness units might use the same under­ly­ing fin­an­cial data but cal­cu­late oper­at­ing profit dif­fer­ently or organ­ize their P&L reports accord­ing to dif­fer­ent report­ing standards.

With a con­fig­ur­a­tion-driven approach, both busi­ness units can use their pre­ferred defin­i­tions and report­ing struc­tures without chan­ging the under­ly­ing tech­nical implementation.

2. Solu­tion Architecture

The solu­tion uses three con­fig­ur­a­tion tables, each respons­ible for a dis­tinct part of the report­ing logic.

2.1 Accounts and KPIs

The first table (Fig­ure 1.) defines the rela­tion­ship between fin­an­cial accounts and KPIs. It spe­cifies which accounts con­trib­ute to each KPI, allow­ing the com­pos­i­tion of a KPI to be changed without modi­fy­ing the under­ly­ing pro­cessing code.

Fig­ure 1. Account and KPIs map­ping with con­fig­ur­a­tion ID.

For example, a rev­enue KPI might include sev­eral account groups. If the busi­ness changes which accounts belong to rev­enue, the map­ping can be updated in the configuration.

Dur­ing data pro­cessing, account-level val­ues are assigned to the cor­res­pond­ing KPIs accord­ing to these mappings.

2.2 Cal­cu­la­tions and Hierarchy

The second table (Fig­ure 2.) defines how P&L hier­archy ele­ments are cal­cu­lated from KPIs.

Fig­ure 2. The P&L definition.

It sup­ports aggreg­a­tion-based cal­cu­la­tions, such as sum­ming sev­eral KPIs, as well as ratio cal­cu­la­tions, where one KPI serves as the numer­ator and another as the denominator.

For example, a profit mar­gin could be cal­cu­lated by divid­ing profit by rev­enue. By stor­ing the cal­cu­la­tion defin­i­tion in con­fig­ur­a­tion, the solu­tion can sup­port dif­fer­ent cal­cu­la­tion rules without requir­ing a new imple­ment­a­tion for each KPI or P&L element.

This cre­ates a con­sist­ent way to man­age fin­an­cial cal­cu­la­tions while allow­ing busi­ness defin­i­tions to evolve.

2.3 P&L Presentation

The third table (Fig­ure 3.) con­trols how the P&L is presen­ted in Power BI.

Fig­ure 3. The P&L view definition.

It defines which KPIs and hier­archy ele­ments appear in the report, how they are grouped, and where they sit within the hier­archy. It also stores dis­play labels and sort orders, sup­port­ing up to six con­fig­ur­able hier­archy levels.

This sep­ar­a­tion is import­ant because the way fin­an­cial inform­a­tion is cal­cu­lated does not neces­sar­ily determ­ine how it should be presented.

For instance, one report might group res­ults by busi­ness unit, while another organ­izes them by rev­enue, costs, and prof­it­ab­il­ity. Both can use the same under­ly­ing data while present­ing it accord­ing to dif­fer­ent busi­ness requirements.

3. The Semantic Data Model

The semantic data model con­nects the pro­cessed fin­an­cial val­ues with the con­fig­ur­able busi­ness definitions.

At the backend level, account-level fin­an­cial val­ues are aggreg­ated and assigned to the cor­res­pond­ing KPIs. The res­ult­ing val­ues are stored in a fact table, which ref­er­ences KPIs through their IDs.

Dimen­sion tables provide the inform­a­tion required to organ­ize these val­ues, includ­ing the P&L hier­archy and the rela­tion­ships between KPIs and hier­archy elements.

Power BI uses the KPI IDs to retrieve and aggreg­ate the rel­ev­ant val­ues from the fact table. The dimen­sion tables provide the hier­archy, labels, and order­ing needed to dis­play the res­ults in a P&L matrix.

The key advant­age is that the fin­an­cial data and its present­a­tion are decoupled. Chan­ging an account-to-KPI map­ping, adjust­ing a cal­cu­la­tion, or restruc­tur­ing the hier­archy does not inher­ently require the under­ly­ing fin­an­cial val­ues to be changed.

Instead, the rel­ev­ant con­fig­ur­a­tion is updated and incor­por­ated into the pro­cessing and report­ing workflow.

This allows the same fin­an­cial data to sup­port mul­tiple busi­ness per­spect­ives without duplic­at­ing the entire data model or cre­at­ing sep­ar­ate reports for every variation.

4. Man­aging Mul­tiple Con­fig­ur­a­tions and Versions

Dif­fer­ent users and busi­ness areas often have dif­fer­ent report­ing require­ments. To accom­mod­ate this, every KPI and P&L setup is assigned a unique con­fig­ur­a­tion ID.

This ID iden­ti­fies the set of busi­ness defin­i­tions used for a par­tic­u­lar report­ing scen­ario. It keeps account map­pings, cal­cu­la­tion rules, and present­a­tion set­tings asso­ci­ated with the appro­pri­ate configuration.

Mul­tiple con­fig­ur­a­tions can coex­ist within the same solu­tion, allow­ing dif­fer­ent users to view the P&L accord­ing to their indi­vidual requirements.

For example, a fin­ance team could main­tain a stand­ard cor­por­ate P&L while a busi­ness unit uses a cus­tom­ized hier­archy and KPI defin­i­tion. Both con­fig­ur­a­tions can ref­er­ence the same under­ly­ing fin­an­cial data.

This also makes con­fig­ur­a­tion changes easier to man­age. New defin­i­tions can be intro­duced or exist­ing ones adjus­ted without build­ing an entirely sep­ar­ate report­ing solution.

The con­fig­ur­a­tion ID provides the mech­an­ism for dis­tin­guish­ing these defin­i­tions; appro­pri­ate fil­ter­ing and pro­cessing logic ensure that each report uses the inten­ded configuration.

5. Key Bene­fits of the Approach

The con­fig­ur­a­tion-driven design offers sev­eral prac­tical advantages:

• Flex­ib­il­ity: KPI defin­i­tions, cal­cu­la­tions, and P&L hier­arch­ies can evolve as busi­ness require­ments change.
• Reusab­il­ity: Mul­tiple report­ing scen­arios can share the same under­ly­ing data and tech­nical framework.
• Main­tain­ab­il­ity: Busi­ness defin­i­tions are sep­ar­ated from pro­cessing logic and the semantic model, redu­cing the need for code changes for con­fig­ur­a­tion-level adjustments.
• Con­sist­ency: A com­mon pro­cessing frame­work helps apply cal­cu­la­tion and map­ping rules consistently.
• Scalab­il­ity: Addi­tional con­fig­ur­a­tions can be intro­duced without cre­at­ing a com­pletely sep­ar­ate solu­tion for each busi­ness unit.

There is one import­ant con­sid­er­a­tion: con­fig­ur­a­tion changes do not elim­in­ate every tech­nical depend­ency. New cal­cu­la­tion types, changes to the under­ly­ing data struc­ture, or unsup­por­ted report­ing require­ments may still require devel­op­ment. The bene­fit is that changes within the sup­por­ted con­fig­ur­a­tion frame­work can be man­aged without modi­fy­ing the core implementation.

6. Con­clu­sion

The object­ive of this pro­ject was to build a flex­ible P&L report­ing frame­work in which busi­ness logic and report present­a­tion can be adap­ted inde­pend­ently of the under­ly­ing fin­an­cial data.

By sep­ar­at­ing account map­pings, KPI cal­cu­la­tions, hier­archy defin­i­tions, and present­a­tion set­tings into con­fig­ur­a­tion tables, we cre­ated a solu­tion that sup­ports mul­tiple report­ing require­ments within a shared Dat­ab­ricks and Power BI architecture.

The res­ult is a more adapt­able report­ing envir­on­ment where users can define how fin­an­cial inform­a­tion is cal­cu­lated, struc­tured, and dis­played without requir­ing a sep­ar­ate tech­nical imple­ment­a­tion for every variation.

The key takeaway: A con­fig­ur­a­tion-driven approach turns the P&L from a fixed report into a reusable report­ing frame­work – one that can evolve with busi­ness needs while keep­ing its under­ly­ing data archi­tec­ture stable.