Power BI - Report Layer - Basic Aggregations

One of the things that people must understand is that Power BI is a columnar database, everything is optimised to work with columns. This makes perfect sense when you consider that aggregations - the basis of Analytics - run primarily on columns. Think about it... Total Sales is a sum of the sales column, Average Sale is the average of the sales column and so on and so forth. Around those we apply limits, Total Sales in Nov-2020, for example, is the sum of Sales where the date of sale is in Nov-2020.

Learning the write basic aggregations is the starting point with DAX, even out 325 million row dataset has a major measure that is used repeatedly of Sum(total amount). Start with basics and an efficient data model and you can achieve a great deal.

Simple aggregations

AVERAGE ( ) - Average (mean) of a Column
AVERAGEA ( ) - Average (mean) of a column, but can handle non-numeric values (treats them as 0)
COUNT ( ) - Count the number of items in a column (not including blank or Boolean)
COUNTA ( ) - Count the number of items in a column (not including blank)
COUNTBLANK ( ) - Count the number of blank items in a column
COUNTROWS ( ) - Count the rows in a table
DISTINCTCOUNT ( ) - Count the number of unique Values in a column (including blank)
MAX ( ) - Returns the largest numeric value in a column
MIN ( ) - Returns the smallest numeric value in a column
SUM ( ) - Arithmetic total

https://geordieintelligence.co.uk/ww001

#PowerBI #BusinessIntelligence #DataAnalytics