Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
@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])
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |