Forum Discussion

Sharma0815's avatar
Sharma0815
Helper II
3 years ago

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

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...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

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's avatar
Sharma0815
Helper II
3 years ago

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.

IncidentNote_DateInteractionsClosed_DateNote_Added_byRegion 
34211/23/202312/3/2023ITNorth
34211/24/202322/3/2023ITNorth
34211/24/202322/3/2023SalesNorth
34211/25/202342/3/2023ITNorth
34211/26/202322/3/2023ITNorth
34221/26/202322/4/2023ITWest
34221/27/202342/4/2023MarketingWest
34221/27/2023102/4/2023HRWest
34231/23/202372/5/2023HRSouth
34231/23/202312/5/2023ITSouth
34231/23/2023172/5/2023ITSouth
34241/28/202342/6/2023HREast
34241/29/202382/6/2023ITEast
34251/30/2023302/6/2023ITEast
34261/23/2023322/6/2023HREast
34271/24/2023502/6/2023HREast
34281/23/2023622/6/2023ITEast
 
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's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    Share the download link of the PBI file.

    • Sharma0815's avatar
      Sharma0815
      Helper II
      3 years ago

      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