Help with Median
Greetings! I've been trying to figure out how to get a 3-month median, but I'm stumped. Here's my sample data set. Basically, for 6/2025 MONTH_END, I'd expect the value to display 157 since that's the median for date ranges 4/2025 - 6/2025. Best I've done is: R3M_MEDIAN_VALUE = CALCULATE( MEDIANX('R3M',[VALUE]), DATESINPERIOD(R3M[MONTH_END], MAX(R3M[MONTH_END]), -3, MONTH) ) But it's probably not filtering correctly. Really appreciate any help. Thanks in advance! 🙂
Median of the Total from Each Month
The current output of my measure is the total count across all months. I want to adjust my measure so that the output is the Median of the total for each month. Current Output: Output = 229 Desired Output: July: 56 August: 56 September: 60 October: 57 Median = 56.5 My measure is exluding November and December, so sometimes the median will be across 4 months, sometimes 5 months, and sometimes 6 months, depending on the time of year. Measure 3 = VAR SelectedDate = SELECTEDVALUE('LOI Date'[Days Until EOM Sort]) VAR CurrentTime = TIME(HOUR(UTCNOW()) - 5, MINUTE(UTCNOW()), SECOND(UTCNOW())) VAR ThresholdTime = TIME(12, 30, 0) VAR TodayDate = TODAY() VAR StartDate = EDATE(TodayDate, -7) -- 7 months back from today RETURN CALCULATE( DISTINCTCOUNT('Opportunity'[Id]), FILTER( 'Opportunity', IF( CurrentTime > ThresholdTime, 'Opportunity'[Days Until EOM] < SelectedDate, 'Opportunity'[Days Until EOM] <= SelectedDate ) && MONTH('Opportunity'[LOI Date]) = MONTH('Opportunity'[Close Date]) && YEAR('Opportunity'[LOI Date]) = YEAR('Opportunity'[Close Date]) && 'Opportunity'[LOI Date] >= StartDate && 'Opportunity'[LOI Date] <= TodayDate && NOT (MONTH('Opportunity'[LOI Date]) IN {11, 12}) -- Exclude Nov and Dec ) )
Create Median measure for Counting Absent Data as 0
I am having trouble calculating the median number of a column, where each row is a different hour of a day, but there may be missing dates/hours that I'd like to count as 0 in my median function. It's difficult to explain, so I put a table demonstrating my issue below, covering 5 days of the year (1/1/2023-1/5/2023). NumStudies is the number of studies done during the given date/time. I would like to calculate for the given timeframe (1/1-1/5 in this case), what is the median number of studies done for every hour of the day? If there is no date/time in the table (for example on 1/1 there were no other studies except at 1:00 pm and 8:00 pm) I would like that to count as 0 when I calculate the median studies done for each hour for the given time frame. Source Data: DateTime NumStudies 1/1/23 1:00 PM 1 1/2/23 1:00 PM 3 1/3/23 1:00 PM 5 1/5/23 1:00 PM 7 1/3/23 2:00 PM 1 1/4/23 2:00 PM 2 1/5/23 2:00 PM 3 1/5/23 3:00 PM 5 1/5/23 4:00 PM 10 1/5/23 5:00 PM 20 1/5/23 6:00 PM 30 1/5/23 7:00 PM 40 1/1/23 8:00 PM 100 1/2/23 8:00 PM 110 1/3/23 8:00 PM 120 1/4/23 8:00 PM 130 1/5/23 8:00 PM 140 What I'd like is a measure which calculates the median of the number of studies for each hour, accounting for date/times that may be missing. The results of the median function in this example for 1/1/2023 through 1/5/2023 would show: Results: Hour MedianNumStudies 12:00 AM 0 1:00 AM 0 2:00 AM 0 3:00 AM 0 4:00 AM 0 5:00 AM 0 6:00 AM 0 7:00 AM 0 8:00 AM 0 9:00 AM 0 10:00 AM 0 11:00 AM 0 12:00 PM 0 1:00 PM 3 (i.e. median of 1,3,5,0 (from 1/4/23) ,7) 2:00 PM 1 (median of 1,2,3,0,0) 3:00 PM 0 4:00 PM 0 5:00 PM 0 6:00 PM 0 7:00 PM 0 8:00 PM 120 9:00 PM 0 10:00 PM 0 11:00 PM 0 I've tried a number of different methods but have not been able to come up with anything that works. Any help is much appreciated. Thanks in advance!
Rolling Median Time Period
Hi all, I need to dynamically calculate the median of the last three months of the starting time of the distinct jobs of the current selected date, that is: I want to calculate, for each distinct job key, the median of its "Start" time of the last three months. I tried: MedianStartingL3M = VAR NumberOfDays = 91 VAR MaxDay = SELECTEDVALUE('Fact Jobs'[job_start_datetime]) VAR MinDay = MaxDay-NumberOfDays VAR Result = CALCULATE(PERCENTILE.EXC('Fact Jobs'[job_start_time],0.5),ALL('Fact Jobs'),'Fact Jobs'[job_key]=SELECTEDVALUE('Fact Jobs'[job_key]),'Fact Jobs'[job_start_datetime]<=MaxDay,'Fact Jobs'[job_start_datetime]>MinDay) RETURN Result Where job_start_time = time when the job starts. In the first example: 14:28:49 job_key = unique identifier of the job job_start_datetime = datetime when the job starts. In the first example 15/12/2022 14:28:49 But it's not working Could someone help me out? Thanks in advance for your help. BR, Sara
Calculate the median, but sum in advance.
I have the above bleu table. And I want to generate a (yellow) table that shows the median of "Value" by color. But before calculating the median, "Value" must first be summed to "Color" and "Animal", like the green table above. To make the example table, below the code of the advanced editor: let Bron = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBND4IgGAfw78LZg/iax1LXOtW0m3ONJSKLQWP5/XtoTWBNu/AAvz+vXYdO1S0MMQpQQwdoS/KCNkF98KVoneKFKsV8StYpXejKGdVQscVsC3MYHgSdodSCPicizYUKG9jZQKnVXQ1cUD9R2MSRazKO1LscDq23kjz81dh801kTycz8/nN6ZDX6VWfneFMTV1tK2CwE9FKbSP8mMjdx4ebb4T0L5y43E5cKaoz6/g0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Color = _t, Animal = _t, Value = _t]), #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"ID", type text}, {"Color", type text}, {"Animal", type text}, {"Value", Int64.Type}}) in #"Type gewijzigd"
Service Duration in seconds Calculation in DAX
Hi, I have this code in excel that calculates the service duration (in seconds) of a Support ticket, excluding bank holidays, weekends and out of office hours. Excel Code - =((NETWORKDAYS.INTL(A2,B2,1,$H$2:$H$11)-1)*("18:00"-"7:00")+IF(NETWORKDAYS.INTL(B2,B2,1,$H$2:$H$11),MEDIAN(MOD(B2,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,$H$2:$H$11)*MOD(A2,1),"7:00","18:00"))*86400 My Dax code so far - Measure.ServiceHours = VAR _StartDate = SELECTEDVALUE(TICKET_MASTER[TICKETSUBMITDATE]) VAR _EndDate = SELECTEDVALUE(TICKET_MASTER[CLOSEDTIME]) RETURN ((NETWORKDAYS(_StartDate, _EndDate,1,BankHolidayDates)-1)*("18:00"-"7:00")+IF(NETWORKDAYS(_EndDate,_EndDate,1,BankHolidayDates),MEDIAN(mod(_EndDate,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(_StartDate,_StartDate,1,BankHolidayDates)*MOD(_StartDate,1),"7:00","18:00"))*86400 Unfortunately Median dax code works differently to excel, can anyone help me convert this into DAX? Example of excel code working below
How to find median from values resulted from another measure?
Hello. Ma problem for today is the following: i've got a table visual with some date, and i wan't to get median from it. There are some some companies and measure (it just shows random numbers for example), and i need to get the median from it. The problem that is just don't work. I don't know the way to work with data from a measure, displayed on visual and affected by filters. And if i use MEDIANX (tablename, [measure]) then it will be empty. So, any suggestions? Desired result:
Median of ratio
Hi, I have a table with 3 columns (Item, Price, Quantity) and I need to create a measure that will calculate the median of the ratio between Quantity and Price. Some of the rows do not have Price values. In the table below I added the Ratio column that is the ratio between Quantity and Price for testing the median by calculating the median of the column Ratio. But, I do not want to create the Ratio calculated column. Instead, I want to use a measure that will calculate the ratio and then determine the Median of the ratio and display it at the bottom of the table (where ussually you see the Total for each column). I created two measures m_Ratio1 and m_Ratio2: m_Ratio1 = DIVIDE(MAX('Median Issues'[Quantity]),MAX('Median Issues'[Price])) m_Ratio2 = VAR Ratio_Table = SUMMARIZE ( 'Median Issues', 'Median Issues'[Item], "Ratio1", [m_Ratio1] ) RETURN IF ( HASONEVALUE ( 'Median Issues'[Item] ), [m_Ratio1], MEDIANX ( Ratio_Table, [Ratio1] ) ) Below is the Median results of the two measures and the Median calculated for the Ratio column: The expected result should be 2.26% as determined by calculating the Median of the Ratio column. But, if I do not use the Ratio calculated column, the measures are producing different Medians from what it should be: m_Ratio1 = 2.51% and m_Ratio2 = 1.48%. Please let me now if you can create a measure that will produce the correct Median of a ratio between two columns (without creating a calculated Ratio column). Thank you. Note: this is the table data. You can copy it and paste it in Excel. Item Quantity Price Ratio A1 136,013 4,500,000 3.02 % A2 56,493 2,500,000 2.26 % A3 37,800 4,445,000 0.85 % A4 10,823 3,000,000 0.36 % A5 142,795 5,700,000 2.51 % A6 17,897 850,000 2.11 % A7 103,728 2,750,000 3.77 % A8 99,174 A9 8,743 A10 110,836Calculating median of time by group
Hi. Im having trouble in calculating the median of a column in my proyect. I can't get it to work (no result). Well, the thing it's like this. I have 2 tables, one it's a calendar table and the other one it's my data table. They have a one to many relation. I want to get the median of a column, group by date (month date) like i have my other measures, but i cant get it to work. The values that i want to get the median, are expresed in seconds. So, what i want should be something like this: And it should be able to filter by Consulta1[CANAL] I tried creating a measure in many ways, median (time_total) = MEDIAN(tablaPortabilidades[TPO_TOTAL]) median (time_total) = MEDIANX(tablaPortabilidades, tablaPortabilidades[TPO_ACEPT]) I also tried creating a measure that sums [TPO_ACEPT] and then creating a median of this sum, but same result. The result: Can someone give me a hand? I don't know how to upload my .pbix here so i upload it to my drive. .pbix Here the data was imported, but im usind a direct query connection. Pd: In the pbix the Consulta1 is the same as tablaPortabilidades Thanks!
