You are currently viewing What is an Implicit Measure in Power BI and Excel?

What is an Implicit Measure in Power BI and Excel?

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

Play Video

Creating Implicit Measures in Excel

Create an Implicit Measure 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.

FDTC

The Finance Department Training Company was set up to help organisations improve the function of their finance department. We believe that finance teams are comprised of two things; people and systems. We concentrate on the development of a department’s people, to improve their efficiency and output so that they can better perform their roles.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.