These are some of the most commonly used DAX expressions used in Power BI. Make a comment if you think something is missing!
Total Sales Calculation
Total Sales = SUM('TableName'[SalesAmount])
Total Cost Calculation
Total Cost = SUM('TableName'[Cost])
Profit Calculator
Profit = [Total Sales]-[Total Cost]
Profit Margin
Profit Margin = DIVIDE([Profit],[Total Sales])
Transaction Count (count rows)
Transactions = COUNTROWS('Table')
Related Table Count (total rows in a related table, ex total transactions by Product)
Transactions + COUNTROWS(RELATEDTABLE('TABLE'))
Related - count of sales in the US
Count of sales orders in the US = COUNTROWS(FILTER(ALL('Sales'). RELATED('SalesGeography'[Countries]) = "United Sates"))
YTD/QTD/MTD Sales
Total Sales this Year = TOTALYTD(SUM('Sales'[Sales]), 'Dates'[Dates])
Total Sales this Quarter = TOTALQTD(SUM('Sales'[Sales]), 'Dates'[Dates])
Total Sales tis Month = TOTALMTD(SUM('Sales'[Sales]), 'Dates'[Dates])
MTD Sales
MTD Sales = TOTALMD([Total Sales], 'Date Table'[DateColumn])
MTD Sales (Direct Query)
MTD Sales = CALCULATE (
[Total Sales],
FILTER(
ALL ('DateTable'),
'DateTable[DateYear] = MAX('DateTable'[DateYear])&&
'DateTable[DateMonth] = MAX('DateTable'[DateMonth])&&
'DateTable'[Date] <= MAX('DateTable'[Date])
)
)
YTD Sales
YTD Sales = CALCULATE (
[Total Sales],
FILTER(
ALL('DateTable'),
'DateTable'[DateYear] = MAX('DateTable'[DateYear])&&
'DateTable'[Date] <= MAX('DateTable'[Date])
)
)
Prior Year Profit
Prior Year Profit = CALCULATE([Profit],SAMEPERIODLASTYEAR'DateTable'[DateColumn])
Prior Year Profit (Direct Query)
Prior Year Profit = CALCULATE(
[Profit],
FILTER(
ALL('DateTable'),
'DateTable'[Year] = MAX('DateTable'[Year]) -1
)
)
Year over Year Profit
YoY Profit = [Profit]-[Prior Year Profit]
Last YTD Sales
Last YTD Sales = CALCULATE([YTD Sales], SAMEPERIODLASTYEAR('DateTable'[DateColumn]))
Total Sales for all Countries
Total Sales All Countries = CALCULATE([Total Sales], ALL('Geography Table'[Country]))
Percent of Total Calculation
Percent of Total = DIVIDE([Total Sales], [Total Sales All Countries])
Rolling 12 Month Sales/Profit
Rolling 12 Month Profit = CALCULATE([Profit].
DATESBETWEEN('DateTable'[DateColumn],
NEXTDAY(
SAMEPERIODLASTYEAR(
LASTDATE('DateTable'[DateColumn]))),
LASTDATE('DateTable'[DateColumn])))
7 Day Moving Average Profit (number of days can be changed)
7 Day Moving Average = AVERAGEX(
FILTER(
ALL('DateTable'),
'DateTable'[FullDateAlternateKey] > (MAX('DateTable'[FullDateAlternateKey]) -7 &&
'DateTable'[FullDateAlternateKey] ><= MAX('DateTable'[FullDateAlternateKey])
),
[Profit])
Country Rank (calculated measure to rank a specific column in a table by a measure)
(example is measure Country ranked by measure [Total Sales])
Country Rank = RANKX(ALL('GeographyTable'[Country]),[Total Sales])
Count of sales orders over 100
Count of sales orders over 100 = COUNTROWS(FILTER('Sales','Sales'[Sales]>100))
ALL (ignoring any filters applied)
ALL(<table> or <column>)
Count of all sales orders, removing filters
Count of all sales orders = COUNTROWS(ALL('Sales'))
Calculate with filter
Sum of sales all countries = CALCULATE(SUM('Sales'[Sales]), ALL('SalesGeography'))
Concatinate String
= [ChannelLabel] & " - " &[ChannelName]
= CONCATENATE(CONCATENATE( [ChannelLabel, " - "), [ChannelName]
Converts date type to string
= FORMAT([Datekey], "YYYY-MM-DD")
YY = 19 ,YYYY = 2019
MM = 06, MMM = Jun, MMMM = June
DD = 07, DDD = Mon, DDDD = Monday
Create Date & DateTime type fields
= DATE(2019,6,1)
= DATE(2019,6,1) + TIME(20,15,0)
DAX Example for new Date Table:
Calendar =
VAR _calendar =
CALENDAR ( "1/1/2011", "12/31/2019" )
RETURN
ADDCOLUMNS (
_calendar,
"Year", YEAR ( [Date] ),
"MonthNumber", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Quarter", "QTR " & FORMAT ( [Date], "Q" ),
"QuaterNumber", FORMAT ( [Date], "Q" ),
"MonthYearNumber", FORMAT([Date], "yy mm"),
"Month Year", FORMAT([Date], "mmm yyyy")
Comments