Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
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
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.
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 |
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.
Hi,
Share the download link of the PBI file.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |