Forum Discussion

kaytaylor19's avatar
kaytaylor19
New Member
8 months ago
Solved

Learning to write DAX statements

Help Please!! I have a table named 90 days. In that table there are picking units. I want to create a new measure that will give me the average pick units for each month. There is no date table.   ...
  • jgeddes's avatar
    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