Forum Discussion
Learning to write DAX statements
- 8 months ago
There are many possible ways to write DAX for an average as it will depend upon the context you require.
The most basic way involves no DAX, just measures provide by the UI.
In a table visual, drag the Date field into the columns. PBI automatically applies Date/Time Intelligence to your date column (provided it is formatted as a date). You can then select only the month (year too if you need it). Now drag the Picking Units field into the columns. Choose average as the summerization. You should now have an average by month.
Example:
In the table it results in...
But you asked for DAX. We can write this average as
Average1 = AVERAGEX('Table','Table'[Picking Units])But you may notice the total average of 14.40. That may not be the value you are expecting to see. 14.4 is the average of all the rows in the table regardless of the month as the total row lacks a month context. (It is all months). If you were wanting the total row to display the average of the monthly averages, (20+6)/2 = 13, then we need to re-write the measure.
Average2 = AVERAGEX( SUMMARIZE( 'Table', 'Table'[Date].[Year], 'Table'[Date].[Month], "__avg", AVERAGEX('Table', 'Table'[Picking Units]) ), [__avg] )In this measure we are creating a virtual table that summarizes the initial table by year and month and then calculates the average picking units for each row in the virtual table. The measure then takes the average of those rows.
There are other ways you can calculate measures, but this should give you a decent starting point.
Please check out the Microsoft Learn documentation to learn more.
https://learn.microsoft.com/en-us/dax/summarize-function-dax - 8 months ago
Hi kaytaylor19
Simplify time-related/date-related calculations by using a dedicated dates table marked as a date table and is related to your fact table in a one-to-many relationship.
Please see the attached pbix.
There are many possible ways to write DAX for an average as it will depend upon the context you require.
The most basic way involves no DAX, just measures provide by the UI.
In a table visual, drag the Date field into the columns. PBI automatically applies Date/Time Intelligence to your date column (provided it is formatted as a date). You can then select only the month (year too if you need it). Now drag the Picking Units field into the columns. Choose average as the summerization. You should now have an average by month.
Example:
In the table it results in...
But you asked for DAX. We can write this average as
Average1 =
AVERAGEX('Table','Table'[Picking Units])
But you may notice the total average of 14.40. That may not be the value you are expecting to see. 14.4 is the average of all the rows in the table regardless of the month as the total row lacks a month context. (It is all months). If you were wanting the total row to display the average of the monthly averages, (20+6)/2 = 13, then we need to re-write the measure.
Average2 =
AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Date].[Year],
'Table'[Date].[Month],
"__avg",
AVERAGEX('Table', 'Table'[Picking Units])
),
[__avg]
)
In this measure we are creating a virtual table that summarizes the initial table by year and month and then calculates the average picking units for each row in the virtual table. The measure then takes the average of those rows.
There are other ways you can calculate measures, but this should give you a decent starting point.
Please check out the Microsoft Learn documentation to learn more.
https://learn.microsoft.com/en-us/dax/summarize-function-dax