+ Reply to Thread
Results 1 to 6 of 6

DAX power pivot Average

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    DAX power pivot Average

    I'd like to calculate the average for the total of sales for each month, per sale date not the total number of days per moth.

    So in excel it would be
    Please Login or Register  to view this content.
    summed units / count of sales dates.

    I've used

    Please Login or Register  to view this content.
    To give me the total sales for each month but can't sum the number of dates for each month. I have tried various
    AVERAGE, AVERAGEX option, but cant get the correct granularity,

    I've attached a workbook with attempts and excel table with what I'd like to achieve.

    Richard
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: DAX power pivot Average

    In the attached both these approaches produce the same averages.


    I
    J
    K
    L
    3
    4
    1/1/20
    6.875
    5
    2/1/20
    5.285714
    6
    3/1/20
    4.6
    7
    8
    # of days
    # units
    average?
    9
    1/1/20
    8
    55
    6.875
    10
    2/1/20
    7
    37
    5.285714
    11
    3/1/20
    10
    46
    4.6


    With the first day of each month in Table1 listed in column I this in cell J4 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The other approach (more resembling the one in your upload) again with first day of the month in column I this in J9 and filled down to get the counts.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then to sum units this in K9 and copied down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then for the monthly averages this in L9 and copied down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does this do what you want?
    Dave

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: DAX power pivot Average

    Thank you

    RD

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: DAX power pivot Average

    You are welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  5. #5
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: DAX power pivot Average

    Sorry forgot.

    RD

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: DAX power pivot Average

    No problem.

    Thank you for your response.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Pivot DAX average calculation of a time period
    By Dicken in forum Office 365
    Replies: 4
    Last Post: 10-18-2022, 11:56 AM
  2. [SOLVED] Power Pivot DAX cumulative Average by grouping
    By Dicken in forum Office 365
    Replies: 3
    Last Post: 09-16-2022, 09:53 AM
  3. Replies: 2
    Last Post: 12-17-2021, 08:41 AM
  4. [SOLVED] Power pivot can't calculate average because of text in column
    By beastiaan in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-20-2021, 08:43 AM
  5. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  6. Replies: 15
    Last Post: 03-29-2018, 11:29 AM
  7. Power Pivot or just regular Excel weighted average formula
    By stevemkiidub in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-14-2015, 12:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1