What Is An Implicit Measure in Power BI and Excel?

An implicit measure is an aggregating calculation like a SUM or COUNT that is automatically (or implicitly!) calculated by Power BI or PivotTables in Excel. Understanding implicit measures is a prerequisite for understanding PivotTables and how Power BI aggregates data before you dive into DAX so this is a nice little opener for those topics!

If you use PivotTables then you’ve already seen Implicit Measures in action. Implicit measures are just a fancy term for aggregating (summing/totalling) numerical value fields in PivotTables when you put a numerical value field into the Value window of the Field List Pane. This is great for saving time versus static data tables, where you have to type out a whole SUM calculation, or a SUMIF if you want to total a value field by other categories in your base dataset.

It’s pretty much the same process in Power BI so check out the 2 minute video, to see how to create these measures. If you’d like to follow along with the video then click on the resources link below the video:

Resources

Symbols

Video Chapters

• 00:17 – What is an Implicit Measure?
• 00:44 – How to create an Implicit Measure in Excel
• 01:21 – How to create an Implicit Measure in Power BI

Short On Time?

Instructions

a) Create a pivot table, by highlighting your data and then selecting the ‘Insert’ tab and then selecting ‘Pivot Table’ on the ribbon.

b) In the PivotTable Field List on the right hand side of  the screen, drag and drop the Value column that you want to summarise, and put it in the Values Window of the pane.

c) This automatically calculates the sum of the Value field and puts it in the PivotTable.

Instructions

a) Create a table visualisation by selecting a Table visualisation in the Visualisations Pane

b) In the Data Pane on the right hand side of the screen (used to be called the Fields Pane), drag and drop the Value column and put it in the Values Window of the Visualizations pane.

c) This automatically calculates the sum of the Value field and puts it in the visualization in the Report View.

…and a little more

Aggregating non numerical value fields – if you put a text type field in the Values window of either a Pivot Table or a Power BI report, then both will count the number of rows with text in that field of the dataset rather than SUM it. This is because neither software has the capability to sum text type columns.