Forum Discussion
Drors
Resolver III
8 years agoSum monthly Average - DAX HELP
Hi,
I have a table like :
Date CustomerID TotalPrice
and I have create 3 measures :
1. Sum of total price
2. Count of distinct customer
3. avarege - measure 1 / measure 2
When I use it on a chart with yearmonth on the X axis everything works fine.
But I want to sum that avg for a year view.
I mean if I have on
jan2018 -avg= 100
feb18 - avg =200
march18 - avg =250
I will want to see on 2018 sumavg 550
if I just put the year on the X axis it will make avg for the all year and not only by month
its make a problem because i have diffrent number of customers each month and it will not reflect the real snapshot for my bussiness.
Thanks for your help.
Hello,
you could try to SUMMARIZE and SUMX:
YearAvg= SUMX( SUMMARIZE(Table,
[YearColumn],
[MonthColumn],
"Sum of total price",SUM([Price],
"CountOfCustomer",DISTINCTCOUNT([CustomerID])),
[Sum of total price]/[CountOfCustomer])Best regards