How to include the ALL function in the Medianx calculation using summarized virtual table columns

[Sharma0815]

I am attempting to plot a histogram distribution of incidents by sum of interactions for a selected closed date range and region, using filters. I have created a calculated column to tally the sum of interactions for each incident. My goal is to calculate the median of the sum of interactions for all incidents closed within the selected date range and region, and to display this median as a line on a line and stacked column chart.

Here is the histogram I am trying to plot with the distribution of incidents by sum f interactions created bins and the  median value of the sum of interactions as a line for all incidents closed between 2/3 to 2/6 

Here I am expecting 27.5

Data set

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

SumofNotes =

SUMX (

FILTER ( ALL ( incident_interactions ), [incident] = EARLIER ( [incident] ) ),

incident_interactions[Interactions]

)

Median of Interactions =

VAR T1 =

SUMMARIZE(

incident_interactions,

incident_interactions[Incident],

"Sum of Notes", MIN( incident_interactions[SumofNotes] )

)

RETURN

MEDIANX(T1, [Sum of Notes] )

[ppm1]

This measure gets your expected 27.5 median across all incidents. Note that to get dynamic binning, you'll need a disconnected bin table (not a calculated column, unless you want static binning outside of filter context).

Median Sum =
MEDIANX (
    DISTINCT ( incident_interactions[Incident] ),
    CALCULATE ( SUM ( incident_interactions[Interactions] ) )
)

Pat

[Sharma0815]

Thanks @ppm1 ,I tried your solution and attempted to plot it as a line; however, I expected it to be a straight line with a value of 27.50

[Ashish_Mathur]

Hi,

Your question is not clear.  Could you put the data and workings in an MS Excel workbook (with your formulas, Pivot Tables and explanatory notes).  I will try to understand the logic from there and write the DAX formula.

[Sharma0815]

Hi @Ashish_Mathur ,

I am trying to create a histogram to show the distribution of incidents based on the sum of interactions. My data set, called "incident_interactions", contains incidents data with the number of actions noted each day, as well as the incident's closed date. I have created a calculated column to sum up the interactions for each incident, for example, incident 3421 has 11 interactions.

This is my data set.

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

Sum of interactions =

SUMX (

FILTER ( ALL ( incident_interactions ), [incident] = EARLIER ( [incident] ) ),

incident_interactions[Interactions]

)

I have also created bins for the sum of interactions, with a bin size of 15. The goal is to plot a histogram that displays the number of incidents that fall into each bin and to add a median value line to the chart. The median value is calculated as the median of the sum of interactions for all incidents closed in the selected date range and region, and it is expected to be 27.50

I expect my measure to compute the median of the sum of interactions in the list.

Median of Interactions =

VAR T1 =

SUMMARIZE(

incident_interactions,

incident_interactions[Incident],

"Sum of Notes", MIN( incident_interactions[SumofNotes] )

)

RETURN

MEDIANX(T1,[Sum of Notes])

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Sharma0815]

Hi @Ashish_Mathur ,

Unfortunately, I cannot share files through OneDrive or Google Drive as access to these sites is restricted for me. Is there an alternative method, such as emailing the files to you