non weighted average over specific week day

[hanapbi]

I have the following data:

Date	Tansaction ID	Result	Week Day
2/15/2022	1160	Success	3
2/15/2022	1238	Failed	3
2/16/2022	1361	Success	4
2/17/2022	1616	Success	5
2/18/2022	1667	Success	6
2/19/2022	1958	Success	7

My goal is to create the following measure:

For a selected day (which the user will select), calculate the average of 4 previous success rates on the same week day (in other words, look at 4 weeks, take the rate of each day where the week day is the same as the selected and calculate the average over the 4). The 4 days do not include the selected day

I first did the following, which turned out to be wrong:

Measure 1:

Success Count 4 previous weeks =

var selectedwwekDay = SELECTEDVALUE(Transactions[Weekday])

var selectedDate = SELECTEDVALUE(Transactions[Date])

return

CALCULATE(COUNTROWS(Transactions), Transactions[Result ] = "Success", Transactions[Weekday] = selectedwwekDay, Transactions[Date] < selectedDate, Transactions[Date] > selectedDate - 30)

Measure 2:

Count 4 previous weeks =

var selectedwwekDay = SELECTEDVALUE(Transactions[Weekday])

var selectedDate = SELECTEDVALUE(Transactions[Date])

return

CALCULATE(COUNTROWS(Transactions), Transactions[Weekday] = selectedwwekDay, Transactions[Date] < selectedDate, Transactions[Date] > selectedDate - 30)

Mearue 3 to calculate the average:

Last 4 weeks average =

DIVIDE( aMeasures[Success Count 4 previous weeks], [Count 4 previous weeks])

Problem with this measure is that it takes weighted average. If I had more transactions on a specific day, the average will be impacted, while I don't want it.

Example:

If user choses date 3/20/2022, the relevant data is:

Date	Failed	Success	Total	Success Rate
2/20/2022		1	1	100%
2/27/2022		1	1	100%
3/6/2022		2	2	100%
3/13/2022	1	1	2	50%
3/20/2022	2		2	0%

My measure is basically calculating the average over all 4 weeks, regardless of the rate per day: 

5 Success/ 6 Total = 83%

But I am interested in taking the average of rate over 4 days:

Average (100%, 100%, 100%, 50%) = 87.5%

How can I fix my measure of success count in the last 4 weeks?

[hanapbi]

@Jihwan_Kim , thanks a lot for your help.

Wanted to hear your opinion on a slight variation of the problem above. Let's say that instaed of sucess and failure I have 4 different types of transactions and for each one I want to calculate the ratio in the past 4 weeks. Would I be able to modify the above measure to fit all categories or I will need to write the same measure 4 times, each for the relvant ratio?

Thanks!

[Jihwan_Kim]

Hi,

Thank you for your message.

I am not sure how your desired visualization looks like, and I think it depends on this.

If it is OK with you, please share your sample pbix file with describing how you want to see the visualization, and then I can try to come up with the correct solution.

Thank you.

[hanapbi]

Hi,

Unfortunatly new community members are not allowed to attach files. 

The idea is for the user to understand how transactions behaved yesterday and if it is normal (compared to the previous 4 weeks behaviour). Therefore the visualization will look like like this:

a,b,c,d - are the types (instead of success/ failure above).

Thanks!

[Jihwan_Kim]

Hi, 

Sorry to say that without seeing the sample, it is difficult for me to create a sample that suits your business case. In my opinion, it is not the same, because, I think, Total (a+b+d+c) percentage of selected date's ratio can be 100%, but total a+b+c+d of the average ratio of previous four-same-days-in-the-last-four-weeks might not be always 100%.  If you are looking for this, then I think you can use the same measure with just replacing small things in the existing measure. However, if you want to show the total that is always 100%, then I think instead of averaging one day by one day, I suggesting creating a measure in a slightly different way.

By the way, you can also share your file's link ( OneDrive, GoogleDrive, Dropbox, or others...) here to share your sample data or share your sample pbix file.

Thanks.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below, and I hope it helps to have an idea to create measures and apply those to your data model.

Previous 4 weeks same day success ratio avg: =
VAR currentdatedayname =
    MAX ( 'Calendar'[Day Name] )
VAR previous28daystable =
    CALCULATETABLE (
        'Calendar',
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -28, DAY ),
        'Calendar'[Day Name] = currentdatedayname,
        'Calendar'[Date] < MAX ( 'Calendar'[Date] )
    )
VAR successratiocolumn =
    ADDCOLUMNS ( previous28daystable, "@ratio", [Success ratio:] )
RETURN
    AVERAGEX ( successratiocolumn, [@ratio] )

[hanapbi]

Thanks @Jihwan_Kim ,

The measure doesn't give the required result.

Let's take for example 3/30/2022. This is the relevant data (based on the file you provided):

Date	Fail	Success	Total per day	Day rate
3/2/2022	1	2	3	67%
3/9/2022	2	1	3	33%
3/16/2022		3	3	100%
3/23/2022	3		3	0%
3/30/2022	1	2	3	67%

So for the 30/3 the measure should give:

Average (67%, 33%, 100%, 0%) = 50%

Your measure gives 66.67%

Is it now more clear what am I trying to do?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you very much for your feedback.

I tried to fix the measure.

The problem in my measure was, 

- if the success ratio returns zero, the result shows blank and the the blank was not considered in the averagex function. (I fixed the Succes ratio measure).

-The period ( last four weeks ) was not correctly shown.

Please check the attached pbix file if it is correctly showing the numbers.

Success ratio: = 
DIVIDE( [Success count:], COUNTROWS(Data)) + 0

Previous 4 weeks same day success ratio avg: = 
VAR currentdatedayname =
    MAX ( 'Calendar'[Day Name] )
VAR previous28daystable =
    CALCULATETABLE (
        'Calendar',
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -29, DAY ),
        'Calendar'[Day Name] = currentdatedayname,
        'Calendar'[Date] < MAX ( 'Calendar'[Date] )
    )
VAR successratiocolumn =
    ADDCOLUMNS ( previous28daystable, "@ratio", [Success ratio:] )
RETURN
    AVERAGEX ( successratiocolumn, [@ratio] )