cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## Calculate average from detail table grouped by account

I am fairly new to DAX and could use some help.  I have a detailed transaction table that has one or more records per day for an account that should a transaction amount.  What I would like to end up with is a chart or table that would then show a monthly average per account based on the filters.

Sample Data:

 Account Date Amount 1 1/1/2023 5 1 2/1/2023 10 2 1/5/2023 40 2 1/5/2023 5 2 2/10/2023 30 3 3/1/2023 25 3 4/15/2023 75 4 4/1/2023 100

I would like my results to look like this:

 Month Average per account Jan 25 Feb 20 March 25 April 87.5

Of course, I would also like it to be based on the filters (accounts and dates) that I select.

Thanks in advance for the help.

1 ACCEPTED SOLUTION
New Member

Given your table is named as "Transactions", you can calculate the average per account per month using a DAX measure. First, you need to create a new calculated column "Month" in your table:

Month = FORMAT(Transactions[Date], "MMM")
Then DAX measure:
AveragePerAccountMonth =
AVERAGEX(
SUMMARIZE(
Transactions,
Transactions[Account],
Transactions[Month],
"MonthlyAmount", SUM(Transactions[Amount])
),
[MonthlyAmount]
)

2 REPLIES 2
New Member

Given your table is named as "Transactions", you can calculate the average per account per month using a DAX measure. First, you need to create a new calculated column "Month" in your table:

Month = FORMAT(Transactions[Date], "MMM")
Then DAX measure:
AveragePerAccountMonth =
AVERAGEX(
SUMMARIZE(
Transactions,
Transactions[Account],
Transactions[Month],
"MonthlyAmount", SUM(Transactions[Amount])
),
[MonthlyAmount]
)

Frequent Visitor

@Vitalii777   That's exactly what I was looking for, I just wasn't sure how to get the syntax right.  Thanks!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors