Forum Discussion
Calculate average based on condition
Hi Team,
How can I calculate the average in the following scenario:
| Name | Status | Date |
| ABC1 | S1 | 1/15/2018 |
| ABC1 | S2 | 2/1/2018 |
| ABC1 | S3 | 2/5/2018 |
| ABC1 | S5 | 4/2/2018 |
| ABC2 | S1 | 2/3/2018 |
| ABC2 | S2 | 3/19/2018 |
| ABC2 | S3 | 4/23/2018 |
| ABC2 | S4 | 4/29/2018 |
| ABC2 | S5 | 5/3/2018 |
In this case, the average number of days needs to be calculated between S3 and S5, if S4 is not available. If S4 is present, the average days should be between S4 and S5. How can I write a single measure for this?
In the above example, output will be: Date diff between S3 and S5 = 56 days and Date diff between S4 and S5 = 4 days. Final Average = (56+4)/2 = 30 days
Hi Anonymous ，
Create a calculated column as below:
S4 search = SEARCH("S4",'Table'[Status],1,0)Then create 2 measures as below:
Measure = var _datediff1=DATEDIFF(CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S3"&&'Table'[Name]=MAX('Table'[Name]))),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S5"&&'Table'[Name]=MAX('Table'[Name]))),DAY) var _datediff2=DATEDIFF(CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S4"&&'Table'[Name]=MAX('Table'[Name]))),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S5"&&'Table'[Name]=MAX('Table'[Name]))),DAY) var _sum=SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])),'Table'[S4 search]) Return IF(_sum>=1,_datediff2,_datediff1)average = var _count=DISTINCTCOUNT('Table'[Name]) VAR _TOTAL=SUMX(VALUES('Table'[Name]),'Table'[Measure]) Return DIVIDE(_TOTAL,_count)And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
Hi Anonymous ，
Create a calculated column as below:
S4 search = SEARCH("S4",'Table'[Status],1,0)Then create 2 measures as below:
Measure = var _datediff1=DATEDIFF(CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S3"&&'Table'[Name]=MAX('Table'[Name]))),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S5"&&'Table'[Name]=MAX('Table'[Name]))),DAY) var _datediff2=DATEDIFF(CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S4"&&'Table'[Name]=MAX('Table'[Name]))),CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Status]="S5"&&'Table'[Name]=MAX('Table'[Name]))),DAY) var _sum=SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])),'Table'[S4 search]) Return IF(_sum>=1,_datediff2,_datediff1)average = var _count=DISTINCTCOUNT('Table'[Name]) VAR _TOTAL=SUMX(VALUES('Table'[Name]),'Table'[Measure]) Return DIVIDE(_TOTAL,_count)And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!Anonymous Probably could do something like:
Measure = VAR __FromStatus = IF("S4" IN SELECTCOLUMNS('Table',"Status",'Table'[Status]),"S4","S3") VAR __ToStatus = "S5" VAR __From = MAXX(FILTER('Table',[Status] = __FromStatus),[Date]) VAR __To = MAXX(FILTER('Table',[Status] = __ToStatus),[Date]) RETURN (__From - __To) * 1. Average = VAR __Table = SUMMARIZE('Table',[Name],"Days",[Measure]) RETURN AVERAGEX(__Table,[Measure])
