cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver III
Resolver III

Sum 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Sum monthly Average - DAX 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

View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

Re: Sum monthly Average - DAX 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

View solution in original post

Highlighted
Resolver III
Resolver III

Re: Sum monthly Average - DAX HELP

Hi,

 

@Floriankx Thank you, it works

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors