Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ADB8335
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:

AccountDateAmount
11/1/20235
12/1/202310
21/5/202340
21/5/20235
22/10/202330
33/1/202325
34/15/202375
44/1/2023100

 

I would like my results to look like this:

MonthAverage per account
Jan25
Feb20
March25
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
Vitalii777
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]
)





View solution in original post

2 REPLIES 2
Vitalii777
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]
)





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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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