- Analytics & Reporting
Create Your Power BI Measures Using These 3 Best Practices
By leveraging Power BI reporting, you gain valuable insights that support your day-to-day decision-making. You can analyse your current performance, substantiate business decisions, and measure their impact. However, before you can reap the benefits of accurate reporting, you first need to build it.
When developing your reports, it is important to work in a thoughtful and structured manner. This applies not only to the design of your data model and the organisation and cleansing of your data, but also to the creation of your measures.
What Are Measures?
Measures are calculations or formulas that transform raw data into meaningful KPIs. They process your data by performing calculations such as addition, subtraction, multiplication, division, filtering, and much more before the results can be visualised.
Examples include:
- Revenue
- Revenue growth or decline (%)
- Customer credit days outstanding
- Expected inventory levels within 60 days
- Actuals, budget, and variance for your management P&L
- ...
Tips for Creating Measures
Tip 1: Clearly Define the Scope and Organise Your Measures into (Sub)Folders
To keep your measures organised and easy to navigate, it is recommended to store them in a dedicated Measures table with relevant subfolders. This makes it much easier to locate a measure later, whether you need to update it or use it in a visual.
Creating this folder structure in Power BI is surprisingly less intuitive than you might expect. Start by selecting Enter Data from the Home ribbon. Power BI will prompt you to create a new table. Leave the table itself empty and simply assign it a name before clicking Load.
The new table will appear alongside your existing tables. Add your first measure to this table, for example Test | Revenue.
Next, remove the empty column by selecting Delete from Model. Then switch to the Model View, select the measure you created, and populate the Display Folder property. Power BI will automatically create the folder and place the measure inside it.
Once this structure is in place, future measures can easily be assigned to the appropriate folder by dragging and dropping them. Please note that moving measures between folders and tables can only be done within the Model View.
Tip 2: Document your measures
Some KPIs require more advanced measures. These may combine data from multiple tables, reference other measures, or rely on more complex DAX functions. As a result, it is perfectly normal to forget exactly how a measure was built after a few weeks or months.
By pressing Shift + Enter, you can create a new line within a measure. Entering '//' allows you to add comments without affecting the calculation itself.
This enables you to enrich your measures with valuable documentation that may prove useful later. Examples include:
- Explanations of DAX functions used
- Notes about specific tables or datasets
- The rationale behind certain filters
- Descriptions of the measure's purpose and business meaning
Adding comments may feel unnecessary when building reports, but it can save a significant amount of time when maintaining or extending them in the future.
Tip 3: Define the Measure Format Immediately
Whenever you create a measure, Power BI automatically assigns a format. In many cases, this will be the General format, which displays values in their simplest form.
This means that numbers will lack currency symbols, percentage signs, thousand separators, or decimal formatting. For example, a revenue value of €5,125,000.00 may simply appear as 5125000.
Because reports often contain a large number of measures, it is best practice to assign the correct format immediately. Doing so avoids the need to revisit each measure later to make formatting adjustments.
Power BI provides several predefined formats for:
- Whole numbers
- Decimal numbers
- Percentages
- Currencies
- And other common data types
In addition, you can create your own custom formats. Custom formats specify how positive, negative, and zero values should appear by defining each format section and separating them with semicolons.
This often results in greater consistency than the standard formatting options.
For example, the predefined currency format may display:
- € 515.15 as € 515.15
- € 515 as € 515
For consistency, many organisations prefer to display the latter as € 515.00.
Consider the custom format:
€ #,##0.0;(€ #,##0.0);€ 0.0
This format will display values as follows:
- Positive values: Euro sign, thousand separators, and one decimal place (including trailing zeros)
- Negative values: Parentheses instead of a minus sign, Euro sign, thousand separators, and one decimal place
- Zero values: Euro sign with one explicit decimal place
More information about custom formatting options is available in the Microsoft-documentation.
Conclusion
By applying these simple best practices, you can create a well-structured Power BI reporting environment. One that is not only effective on the surface but also robust behind the scenes.
A structured approach makes it easier to find, understand, maintain, and update your measures over time, ensuring your reports remain scalable and sustainable as they grow.
Looking for support in building your Power BI reports? Need deeper insight into your business data? We'd be happy to help.
