Forum Discussion

Drors's avatar
Drors
Icon for Resolver III rankResolver III
8 years ago
Solved

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.

  • 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

2 Replies

  • 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