cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Drors 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
Floriankx 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
Floriankx 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

Drors Resolver III
Resolver III

Re: Sum monthly Average - DAX HELP

Hi,

 

@Floriankx Thank you, it works

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors