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
Bookmarks