Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |