Forum Discussion

mertsch's avatar
mertsch
Frequent Visitor
10 years ago
Solved

Calculate an average from a Sum by Month

Hi Guys,    I want the average from a time Period. In one Table i have 2 Rows like this:     Date:                  Values:                 01.01.2016        100 01.01.2016        50 01.01.20...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    Hi Mertsch,

     

    I have tested it on my local environment, you can add calculated column to display the month name using the DAX below
    Month = MONTH(MonthAverage[Date])

    And then create a measure
    MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month])

    Regards,

    Charlie Liao

nikil's avatar
nikil
Icon for Resolver I rankResolver I
10 years ago

Looks like you want to get the average by Summing the amount and dividing it by the number of months/distinct dates rather than dividing by the number of rows. 

This might help, write following DAX measures:

Amount=SUM([Values])

NumOfDistinctDates=DISTINCTCOUNT([Date])

Avg=DIVIDE([Amount],[NumOfDistinctDates])

 

 

nikil

Check out the Chicagoland Power BI User Group

mertsch's avatar
mertsch
Frequent Visitor
10 years ago

well it wokrs for the full tabel but if i used filters it dont work for it. 

 

( 140 is correct for all data ) 

 

 

 

But after using Filters it will be still 140.  it need to be 60 in that case 

 

 

 

There must be a other way to have it more flexible with using filters

The data need to be calculated acording to the used filters and visual in front end.

 

May it works with GROUPBY Month Dates?! But i dont get the DAX work

= GROUPBY (Tabelle1;Tabelle1[Month];“TEST2”;SUM(CURRENTGROUP();Tabelle1[Total])) 

 

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi Mertsch,

     

    I have tested it on my local environment, you can add calculated column to display the month name using the DAX below
    Month = MONTH(MonthAverage[Date])

    And then create a measure
    MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month])

    Regards,

    Charlie Liao

    • Kinsey's avatar
      Kinsey
      Icon for Advocate III rankAdvocate III
      9 years ago

      This does not work if the data covers a period longer than a year as multiples will appear over multiple years, is there another way?

      • drmbrklyn's avatar
        drmbrklyn
        Frequent Visitor
        9 years ago

        substitute the followign for the month formula:  =format([date], "YYYYMM")

    • mertsch's avatar
      mertsch
      Frequent Visitor
      10 years ago

      Thats what im looking for ! 

      Thanks a lot ! 

       

    • calerof's avatar
      calerof
      Icon for Impactful Individual rankImpactful Individual
      10 years ago

      Hi,

      What if we want to use this measure with a date drill down, how would it work?

      Fernando