Learning to write DAX statements

[kaytaylor19]

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 · Accepted solution]

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 

[v-prasare]

Hi @kaytaylor19,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @kaytaylor19,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[krishnakanth240]

Hi @kaytaylor19 

Assumption

Your table 90 days has:
Date (date datatype)
Picking Units (numeric)

Correct & Simple Solution (Measure)
Step 1: Create a Year-Month column (optional but recommended)

Calculated column:
YearMonth =
FORMAT ( '90 days'[Date], "YYYY-MM" )

This ensures correct monthly grouping.
Step 2: Create the Average Picking Units measure
Avg Picking Units Per Month :=
AVERAGEX (
VALUES ( '90 days'[YearMonth] ),
CALCULATE ( SUM ( '90 days'[Picking Units] ) )
)

How this works
VALUES(YearMonth) → gets each distinct month
SUM(Picking Units) → totals per month
AVERAGEX → averages across months

[danextian · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Create a Calendar table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a many to One relationship from the Date column of the 90 Days table to the date column of the Calendar table.  To your visual, dray Year and Month name from the Calendar table.  Write this measure

Avg = average('90 days'[picking units])

Hope this helps.

[Amar_Kumar]

@kaytaylor19 

Use this measure 

Avg Pick Units per Month =
AVERAGEX(
SUMMARIZE(
'90 days',
YEAR('90 days'[Date]),
MONTH('90 days'[Date]),
"MonthlyTotal", SUM('90 days'[PickUnits])
),
[MonthlyTotal]
)

Pleaee mark this as an accepted solution.

[jgeddes · Accepted solution]

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