What Is An Implicit Measure in Power BI and Excel?
What is an implicit measure?
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 are 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!
Sounds familiar - where have I seen this before?!
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 verus static data tables, where you have to type out a whole SUM calculatio, 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.
Resource Listing
The Video
- 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
Creating Implicit Measures in Excel
It’s really easy to create an implicit measure, or aggregating calculation in a PivotTable. In the picture we have a data table on the left and then a PivotTable to its right hand side. To aggregate, or sum the total of the Invoice Value field in column E:
a) In the PivotTable Fields pane on the right hand side of the screen, drag and drop the Invoice Value column and put it in the Values Window of the pane.
b) This automatically calculates the sum of the Invoice Value field (£82,027) and puts it in the PivotTable. We can check that the total is correct in the Totals column below the dataset.
Check 3 - Check for trailing spaces
It’s a very similar procedure to create an implicit measure in Power BI. The same dataset has been loaded into Power BI, so to aggregate, or sum
the total of the Invoice Value field in Power BI:
a) In the Fields pane on the right hand side of the
screen, drag and drop the Invoice Value column and put it in the Values
Window of the Visualizations pane.
b) This automatically calculates the sum of the Invoice Value field
(£82,027) 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.
