+ Reply to Thread
Results 1 to 4 of 4

Median in Data Model Pivot When Dollar Amounts Need to be Summed First

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    Maine
    MS-Off Ver
    Excel 2013
    Posts
    8

    Median in Data Model Pivot When Dollar Amounts Need to be Summed First

    Dear all,

    I have yet to find an answer to my question. I am looking for an easy way to get the correct median in my data model pivot, preferably using the DAX method, that will first sum the dollar amounts in the accounts for each user by their ID numbers.

    If I run a straight median, I might get a value like 1,500 in my DAX field, but that is only giving me the median of all rows in the dataset in the amount column. What I really want is a median of the sum of all user account amounts up to the user level before running the median, which should be something closer to 6,550.

    The current formula in the Measures dialog box using DAX is =MEDIAN([Amount]). Could I do something that would sum the amounts first by user ID, then calculate median? I was hoping not to have to transform the data and place it in a separate workbook with one row per user and the amounts summed, but rather use a more efficient shortcut. Unfortunately, I do not know how to get there from here.

    -N
    Last edited by njrgrant; 07-29-2021 at 11:16 AM.

  2. #2
    Registered User
    Join Date
    02-05-2014
    Location
    Maine
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Median in Data Model Pivot When Dollar Amounts Need to be Summed First

    Just to provide some context, I am brand new to DAX, so I don't know what it can and cannot do.

    Here is my current DAX for trying to sum based on the ID numbers before finding the median amounts.

    =MEDIANX (
    ALLSELECTED ( Table1[EMPLOYEE] ),
    CALCULATE ( SUM ( Table1[AMOUNT] ) )
    )

    The problem with that data analysis expression is that when I use it, it aggregates all of my amounts.

    What I want to do is show them according to pre-made data labels for income, which might be 60,000-69,999, and so forth Table1(INCOME_RANGE).

    Is there a fancy DAX way to do that in a pivot table so that it will not just aggregate, but allow disaggregation by fields?

  3. #3
    Registered User
    Join Date
    02-05-2014
    Location
    Maine
    MS-Off Ver
    Excel 2013
    Posts
    8

    Angry Re: Median in Data Model Pivot When Dollar Amounts Need to be Summed First

    Okay, no replies yet, but I think I have it almost solved.

    Here is the new working DAX:
    =MEDIANX (
    SUMMARIZE (
    Range,
    Range[INCOME_RANGE], Range[EMPLOYEE], "AMOUNT",
    SUM ( Range[AMOUNT] )
    ),
    [AMOUNT]
    )

    The problem is that I do not think the MEDIANX function averages the middle two values when there is an even number of values.
    My data check using the regular Excel median formula confirmed that it is not acting as it should.

    Is there a way around this? The standard MEDIAN formula cannot handle it because when I try, "too many arguments" is the error.
    ARGH

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2404 Win 11 Home 64 Bit
    Posts
    23,865

    Re: Median in Data Model Pivot When Dollar Amounts Need to be Summed First

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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] Consolidating Data to one line by summarizing dollar amounts
    By Buzz1126 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-18-2017, 03:45 AM
  2. Turning even Dollar Amounts to odd Dollar Amounts
    By Tim D in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2015, 04:32 PM
  3. Replies: 4
    Last Post: 05-07-2015, 02:56 PM
  4. [SOLVED] Find Median value when totals are summed and not in a range
    By Winwell in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2015, 04:03 AM
  5. [SOLVED] Macro to Reformat Rows that contain date, dollar amounts, and other data
    By dean87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2013, 09:16 AM
  6. dollar amounts
    By atcspaul in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2012, 10:06 PM
  7. Replies: 4
    Last Post: 09-14-2011, 06:23 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