Anyone have any thoughts on how to create incidents per hour per analyst in a group minus null value

[Anonymous]

Hi all,

 I'm attempting to work the following DAX based on the following table called Table1. Basically, I am attempting to get the number of incidents created per hour per member of Group A only and only counting null values of members in the total incident count being divided by. Any thoughts on how to go about this?

Number	Group	Member	State
1	Group A	Tom	Closed
2	Group A	Tom	Closed
3	Group A	null	Open
4	Group A	Richard	Pending
5	Group A	Harry	Pending
6	Group A	Harry	Pending
7	Group B	Jane	Pending
8	Group C	John	Pending

[Greg_Deckler]

I read this through a few times and still not certain I am clear. What would be your expected results from the data you posted? Also, I do not see hour in your data.

[Anonymous]

Hi @Greg_Deckler 

 Thanks for all your assistance previously. You are awesome sir!

 I know I don't have a date or date time column in there, but assume that there is on and these are all (2/29/2020 9:44:12 AM) with each hour incrememented by 1. We will refer to this column as Opened.

 I'm attempting to determine how many incidents were created each hour for a specific group (Group A) divided by the total count of members assigned to incidents. There are null or empty values at times, because the data is live and someone hasn't assigned it yet so member is blank. I still want to include the fact that there IS an incident created at the time however and reflect it in the total count of incidents for whatever time period is selected.

Does this make sense? @Greg_Deckler 

[Greg_Deckler]

So like this? 

Measure = 
    VAR __CurrentGroup = COUNTROWS('Table')
    VAR __AllMembers = COUNTROWS(DISTINCT(ALL('Table'[Member])))
RETURN
    __CurrentGroup / __AllMembers

PBIX is attached.

[Anonymous]

@Greg_Deckler Very cool Greg, close but not quite what I think we are seeking. I've attached a sample PBIX file with test data. This contains more appropriate data.

From what I can tell, this just takes the total incidents or events and then divides them by the distinct count of each analyst name that appears. This is sort of what I'm seeking, except I need to filter it to count ALL incidents (just like the first variable) regardless of whom they are assigned to at the moment - then divide that by the distinct count of names (assigned_to) that appears for just the group (assignment_group) SecOps Analysts (not SecOps Analysts 2 or SecOps Analyst 3).

I then need to somehow take that number and possibly divide it by hours in the day (?) or something - as I'm attemptng to derive how many of these are created per hour per that analyst distinct count from earlier. This is referred to in the PBIX as EPAH or Events Per Analyst Hour 

[Greg_Deckler]

Oh, I think I got my ALL's in the wrong place then if you want ALL incidents regardless of group divided by the distinct members in just a group. Perhaps this:

Measure = 
    VAR __AllIncidents = COUNTROWS(ALL('Table'))
    VAR __AllGroupMembersInContext = COUNTROWS(DISTINCT('Table'[Member]))
RETURN
    __AllIncidents / __AllGroupMembersInContext 

So, ALL incidents regardless of context divided by the members currently within context. 

[Anonymous]

@Greg_Deckler @az38 @MFelix @amitchandak 

So I think I've found about the closest I'm going to get to this answer below, but there are two questions I have

EPAH = COUNTROWS(Table4) / CALCULATE(DISTINCTCOUNTNOBLANK('Table4'[Assigned to]), FILTER(Table4, 'Table4'[Assignment group] = "SecOps Analysts")) / 3 / 8

First, I'm still getting 505 records being counted as an analyst in the second CALCUATE function even though they are blank and I'm using DISTINCTCOUNTNOBLANK, why is this?

Second, is there a means to use this is a KPI as I don't have a trend indicator but I'd like to see if it's up or down each day/week (either is fine). I do have a Date table in the sample PBIX I attached via Github.

[Anonymous]

@Greg_Deckler I did forget to mention that I do have a Date table. I'm thinking I might be able to somehow use summarize or calculate in a function to determine records or incidents opened each day and divide them by 24 to get the EPH (Events or Incident Per Hour) I then can take that number and divide it by distinct count of analysts of the SecOps Analysts group observed per day. I would want to use all of them as below, because they are not all working the same day.

Maybe you have a better idea of what you might use?

How would I modify the following to do this?

Thanks for being a super hero on these forums!

[Greg_Deckler]

Well, I believe the modification would be along the lines of:

Measure = 
    VAR __AllIncidentsInCurrentDateContext = COUNTROWS(ALLEXCEPT('Table','Dates'[Date]))
    VAR __AllGroupMembersInContext = COUNTROWS(DISTINCT('Table'[Member]))
RETURN
    __AllIncidentsInCurrentDateContext / __AllGroupMembersInContext

So, you should just have to modify the first variable to use ALLEXCEPT instead of ALL. ALLEXCEPT respects the specified filters and ignores all other filters. 

[Anonymous]

Getting closer with removing that one null value in the sample PBIX file for Assigned To. Now I have only 505 which are blank.

I'm using the following

EPAH =
VAR __AllIncidentsInCurrentDateContext = COUNTROWS(ALLEXCEPT('Date','Date'[Date]))
VAR __AllGroupMembersInContext = CALCULATE(DISTINCTCOUNTNOBLANK('Table4'[Assigned to]), FILTER(Table4, 'Table4'[Assignment group] = "SecOps Analysts"))
RETURN
__AllIncidentsInCurrentDateContext / __AllGroupMembersInContext

However that filter is not working as expected... The number should be higher as you can see. I'd say once this is determined I can then divide by 3 and then 8

[Anonymous]

Thank you Greg. Let me give that a shot. 

At the moment, I'm attempting to use the following and it has a very convincing number albeit likely not accurate.

EPAH =
VAR __CurrentGroup = COUNT(Table4[Opened].[Date])
VAR __AllMembers = CALCULATE(DISTINCTCOUNT('Table4'[Assigned to]), 'Table4'[Assignment group] = "SecOps Analysts")
RETURN
__CurrentGroup / __AllMembers

[Anonymous]

@Greg_Deckler That's definitely not correct. I have 2336 incidents total and in reality, there are 14 analysts appearing (two as blanks for some reason) - bringing that number to what is shown 2233. I'm attaching the PBIX file again because I'm clearly missing something. 

Logically I would think the formula for this should be

Total number of incidents (with blank or null values) \ Total Number of Analysts in SecOps Analyst Group (no blanks) \ 3 

This gives us per total per shift. I presume we would then divide by 8 (hours in each shift) making closer to 2.6666

https://github.com/R34rvi3w/PowerBI_Test-Data/files/4283448/Events-Per-Analyst-Hour.pbix.zip 

[Anonymous]

Here's the PBIX file, I had to zip and store it on Github as I cannot use Onedrive or any other service. https://github.com/R34rvi3w/PowerBI_Test-Data/files/4278062/EVENTS-PER-ANALYST-HOUR.pbix.zip 

@Greg_Deckler 

[Anonymous]

@Greg_Deckler I think I found something here. Not sure how this will affect performance though. What are your thoughts?

I do have a full DateTime table.

https://community.powerbi.com/t5/Desktop/Grouping-records-per-hour/m-p/716834

Basically, I'm doing almost the same - except grouping them per hour and then per DC of analysts in a specific group (SecOps Analysts) only.