Calculate a drop out rate

[Anonymous]

Hi there

I would like to calculate the drop out rate by date and by page.

How can I achieve this please? Only by Date and Page so there should be only 2 dates for page 2 and 4 dates for page 1.

Thank you so much

DateID Page visitors

01/11/2006	page 1	10
02/11/2006	page 1	9
03/11/2006	page 1	9
04/11/2006	page 1	6
01/11/2006	page 2	3
02/11/2006	page 2	1

[v-shex-msft · Accepted solution]

Hi @Anonymous,

According to your description, you want to get the give up visitors' count and rate, right?

If it is a case, you can refer to below formula to achieve your requirement.

Base table:

Table formula:

Datail Info =
var summary=SUMMARIZE('drop visit id',[Page],"FirstDate",MINX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[DateID]),"LastDate",MAXX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[DateID]),
"MinVisitors",MINX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[visitors]),"MaxVisitors",MAXX(FILTER(ALL('drop visit id'),[Page]=EARLIER([Page])),[visitors]))

var detail=ADDCOLUMNS(summary,"FirstVisitors",MAXX(FILTER(ALL('drop visit id'),'drop visit id'[DateID]=EARLIER([FirstDate])&&'drop visit id'[Page]=EARLIER([Page])),[visitors]),"LastVisitors",MAXX(FILTER(ALL('drop visit id'),'drop visit id'[DateID]=EARLIER([LastDate])&&'drop visit id'[Page]=EARLIER([Page])),[visitors]))

return

SELECTCOLUMNS(detail,"Page",[Page],"FirstDate",[FirstDate],"LastDate",[LastDate],"FirstVisitor",[FirstVisitors],"LastVisitors",[LastVisitors],"MaxVisitors",[MaxVisitors],"MinVisitors",[MinVisitors],
"GiveUp(ByDate)",if([FirstVisitors]-[LastVisitors]>0,[FirstVisitors]-[LastVisitors],0),
"GiveUpRage(ByDate)",if([FirstVisitors]-[LastVisitors]>0,FORMAT(([FirstVisitors]-[LastVisitors])/[FirstVisitors],"Percent"),"0%"),
"GiveUp(ByAmonut)",[MaxVisitors]-[MinVisitors],
"GiveUpRage(ByAmonut)",if([MaxVisitors]>0,FORMAT(([MaxVisitors]-[MinVisitors])/[MaxVisitors],"Percent"),"0%"))

Regards

Xiaoxin Sheng

[Greg_Deckler]

For that data, what would your expected results be?

[Anonymous]

I would like to see average drop out rate of page 1 over time, and page 2 and so on

thanks 🙂

[TristanKuesters]

Don'T you try to set up a measure e.g "DropOut Average" = average(drops) and then use the page no or the date as a lookup value in a matrix, bar chart or whatever?

Ho does the raw data / tables look like?

[Anonymous]

I want to see that the trend by over time as well as by page. Ultimately I would like to see which pages got highest drop out % etc... hope this makes sense?