Bottom N Value to pull the average of another field

[Kmcdonald]

Hi,

I'm trying to work through a formula I'm trying to workout and need a hand...

My data has an event per row, where each line has a unique identifier. At the moment I have measures where I know 95% of the events closed inside any given month, but I want the average number of days open based on the lower 95% of the events.

As shown below, I have sorted the "Number of Days Open" column decending and then added an index column. My thinking is that I could use an Top N function to pull the lower 95% of events, based on the value of the index column. I'm not too familiar with this function at the moment and trying to figure out how to add the measure value, instead of a defined number. THen to also pull the average of the "Number of Days Open" column.

For example in April there are 14 events that were closed, 95% of 14 (rounded) is 13. So I want to pull the average "Number of Days Open", based on the top 13 by value of the index column. Then for each other month to do the same, based on the lower 95% closed in that month.

Can anyone help me please?

[Kmcdonald · Accepted solution]

@Whitewater100 , sure use the link below.

The query "QE Date" is the calendar I created and the other query is the data. 
So the measure of 95% of the events is the "1 - 95% Closed QEs" one in the calendar and the field "_Number of Days Open" is the field I'm trying to get an average on, based on the lower 95% of events by number of days.

Hopefully I"m clear with what I'm explaining...

https://1drv.ms/u/s!AmCPHJJvoRqjoVWVAvghw3qIU_Qo?e=uyPGNJ 

[Kmcdonald · Accepted solution]

@Whitewater100 , sure use the link below.

The query "QE Date" is the calendar I created and the other query is the data. 
So the measure of 95% of the events is the "1 - 95% Closed QEs" one in the calendar and the field "_Number of Days Open" is the field I'm trying to get an average on, based on the lower 95% of events by number of days.

Hopefully I"m clear with what I'm explaining...

https://1drv.ms/u/s!AmCPHJJvoRqjoVWVAvghw3qIU_Qo?e=uyPGNJ 

[Kmcdonald]

@Whitewater100 , I managed to dynamically use the Top N function but the problem I've got now is that the current month isn't showing. 

I used the basic measure below for the average of the number of days open but I'm not sure why it is blank.
The column in the table I've put the rectangle around is one that the measure is defining.

While you've got the file link, would you be able to do a basic average function on the "Number of Days Open" field and see why it won't come up when I use the date field "QE Date [Date]" in a matrix visual?

Either that or if you can see what I've done wrong, I"d appreciate the feedback...

[Whitewater100 · Accepted solution]

Hi:

Thanks for sending the file over. I have put in an alternative Date Table ( if you want to use) as the CALENDARAUTO brings in a huge calendar. You might also want to turn off AutoDate in the Time Intelligence Options (Report Data Load).

I put a couple calculated columns in your Fact Table to get to the cumulative percents the open days were reflecting, so you can zero in on the top 95%, or whichever percent you want to use.

There are a few measures in "Calculations" table.

You are getting to the correct average answer but it's better to make a measure instead of using the implicit way of doing it. (You can see).

The reason April is blank right now is there are no start dates in April yet. 

I brought in a daily view in the event you want to see mid-month when data does come in. Becasue the visual is set up on monthly, it waits to the month is complete to populate the matrix.

I hope this is what you are looking for.

P.S. Later on it would be great if you can split out some dimensional info from the large fact table. Investigator, status and some other fields are good candidates to have their own dimension table so you can more easily sort your data and do more complex analysis. For instance you have say 10 Status. You can make a new table with Called Status with just 10 rows (Status and Status Index(1-10). Then you merge that into your Fact Table on "Status" and just take the index field into your Fact Table. Then join on Status Index and then delete Status from Fact Table. This would give you a new lookup or dimension table. Just some extra comments! Here is file link:

https://drive.google.com/file/d/1H23xON1D3eszex9P9nFji4zocy3ajr2t/view?usp=sharing 

[Kmcdonald]

Hi @Whitewater100 ,

Thank you for that new file and tips, will be definitely be taking that on board 🙂

The thing I can't get my head around is why the average won't show me anything for April. So I went into the data tab and fitered on the "Event Disposition Date (All)" being on and after the 1/4/22, so the closed date.

The number of days is just that closed date minus the start date. Below There is a start date, closed date, and number of days for each line. 

How do I get that to show as an average on my calendar table?

I even changed the date for each month to the start of the month and still didn't get anything...

I read your response again, saying that there needs to be a start date in April for the average to work but shouldn't this be looking at the "Event Disposition Date"?

I'm just wanting an average of the number of days open, compared to the closed date only.

Is there a way to make it do that?

[Whitewater100]

Hi:

I think I understand... Your model was connected on Start Date from Fact Table to the Date Calendar. When I make Disposition date active, then  April populates. I can't find a column called "Closed Date" . I have put in a calculated column to figure the number of days (Date Diff) and you could change it to the field you want to use for Closed Date.

Date Diff = INT('QE Track and Trend'[Initial Event Completion Date] - 'QE Track and Trend'[Start Date])

If you would like the results based on multiple dates we can use the USERELATIONSHIP function.

I mentioned this above and put that calc column in.

"I also see the number of days open is a hard coded figure, do you want that to be bypassed and create a separate measure to calculate [_Number of days Open]?"

I think if the active relationship with Dates is correct (Disposition date to Dates[Date], then these measures should work. I'm just not too sure which columns you prefer to use.

I hope this helps!

I will attach the minor update here. I hope this helps!

Date Diff = INT('QE Track and Trend'[Initial Event Completion Date] - 'QE Track and Trend'[Start Dat... 

[Kmcdonald]

@Whitewater100 , sorry I can't actually use your link for some reason...I was able to get the first one.

The closed date is actually the "Event Disposition Date (All)", I was trying some short hand. Sorry about that.

This is what I've tried:

So this is how the table started looking.

After changing just the relationship:

The table after trying the measure change below:

I also tried the measure below and got the same result:

I'm happy with how my table is for the rest of the data, I just want April to have a running total 😅 . Seems quite difficult for something I was hoping would be simple. 

This measure depends on the average one, it seems as if I have to wait for an event to be started in April to closed to get any stats for that column.
I also tried your INT formula and it gives the same result as my [_Number of Days Open] field.

[Whitewater100]

Hi:

I'm glad those changes helped. I'll post the link again here now..

https://drive.google.com/file/d/1H23xON1D3eszex9P9nFji4zocy3ajr2t/view?usp=sharing 

This is the Running Total Calculated column DAX & the Cumulative % (as the field to your initial question about 95% of open day events.

The figures go into April with changing the relationships.

If you need a measure for Running Total, you can follow this general pattern.

RT =  CALCULATE([MEASURE], FILTER(ALL(DATES), 
          Dates[Date] <= MAXDates[Date]).

As one related suggestion, the more columns you can delete(if not using) on the wide Fact Table, the better the model will perform and easier to find important columns.

I hope all of this has helped!

Are you able to mark this as solved now?

Thanks.

[Whitewater100]

Hi:

Are you able to provide some sample data to help understand the result you'd like?

Thanks..