0

I am trying to plot a histogram to show the distribution of incidents based on the sum of interactions within a selected date range and region center, as filtered by the user. To do this, I have three datasets: incident_interactions, calendar_table, and region_center. The closed_date in the incident_interactions dataset is mapped to the date in the calendar_table dataset.

I have created a column to calculate the sum of interactions for each incident. The formula for the SumofNotes column is:

SumofNotes =
SUMX (
FILTER ( ALL ( incident_interactions ), [incident] = EARLIER ( [incident] ) ),
incident_interactions[Interactions]
)

Next, I want to find the median of the sum of interactions. The formula I am trying for that

Median = CALCULATE(Median(incident_interactions[SumofNotes]), FILTER(calendar_table, calendar_table[Date] >= MIN(calendar_table[Date])), FILTER(calendar_table, calendar_table[Date] <= MAX(calendar_table[Date])), FILTER(region_center, region_center[region center]= region_center[region center]))
enter code here

For example, if the user selects a date range of 2/3/2023 to 2/6/2023 and a region center of all, the sum of interactions for each incident closed in the selected date range and all regions in the region center. Expecting a measure to calculate the median of the sum of interactions

Sumofinteractions   Incident
11                   3421
12                   3424
16                   3422
25                   3423
30                   3425
32                   3426
50                   3427
62                   3428

The median of the SumofNotes here 27.5

 Incident   Note_Date   Interactions    Closed_Date Note_Added_by   Region 
    3421    1/23/2023            1            2/3/2023           IT      North
    3421    1/24/2023            2            2/3/2023           IT      North
    3421    1/24/2023            2            2/3/2023          Sales    North
    3421    1/25/2023            4            2/3/2023           IT      North
    3421    1/26/2023            2            2/3/2023           IT      North
    3422    1/26/2023            2            2/4/2023           IT      West
    3422    1/27/2023            4            2/4/2023       Marketing   West
    3422    1/27/2023           10            2/4/2023           HR      West
    3423    1/23/2023           7             2/5/2023           HR      South
    3423    1/23/2023           1             2/5/2023           IT      South
    3423    1/23/2023          17             2/5/2023           IT      South
    3424    1/28/2023          4              2/6/2023           HR      East
    3424    1/29/2023          8              2/6/2023           IT      East
    3425    1/30/2023          30             2/6/2023           IT      East
    3426    1/23/2023         32              2/6/2023           HR      East
    3427    1/24/2023         50              2/6/2023           HR      East
    3428    1/23/2023         62              2/6/2023           IT      East

Calendar_Table 

Date
2/3/2023
2/4/2023
2/5/2023
2/6/2023

Region center 

Region Center
North
West
South
East
Rahul
  • 467
  • 1
  • 8
  • 24

1 Answers1

1

You could use SUMMARIZE:

=
VAR T1 =
    SUMMARIZE(
        incident_interactions,
        incident_interactions[Incident],
        "Sum of Notes", MIN( incident_interactions[SumofNotes] )
    )
RETURN
    MEDIANX( T1, [Sum of Notes] )
Jos Woolley
  • 8,564
  • 2
  • 4
  • 9
  • Thank you, @Jos Woolley, for your help. I tried the solution you offered, however, it returned a median value of 25.00, which does not match the correct value of 27.50. To clarify, the median is calculated by finding the middle two values of an even list and then taking the average of these two values. – Rahul Feb 11 '23 at 14:39
  • It returns 27.5 for me using the dataset you provided. Of course, it's referencing the *SumofNotes* Calculated Column which you provided, which I presumed you'd added to the *incident_interactions* table. – Jos Woolley Feb 11 '23 at 15:23
  • My bad @Jos Woolley, I have blank rows in the incident_interactions dataset. I removed those, and it is working fine . Thanks a lot for your help. – Rahul Feb 11 '23 at 15:57