Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have three tables time, SectorProject, product Project.
Time AS( SELECT * FROM (VALUES(1011,48),(201,520),(36,120))V(id_project,time)), SectorProject AS( SELECT * FROM (VALUES(1011,'BM'),(1011,'Fi'),(1011,'Om'),(201,'BM'),(36,'BM'))V(id_project,Sector1)), prductProject AS( SELECT * FROM (VALUES(1011,'bike'),(1011,'velo'),(1011,'pc'),(201,'n'),(36, 'r' ))V(id_project,product))
I need to calculate average divided by the count per project
With sql server it will something like the following code:
avg( sum(time)) over (partition by t.id_project) * 1.0 / count(*) over (partition by t.id_project)
How to do the same thing within Dax.
Solved! Go to Solution.
Hi, @Anonymous
Not sure if the result of the record with id_project of 1011 is 4.8 or 5.33333...
If you expected result is 4.8 , please try calculated column as below:
Result_column =
var a=CALCULATE(SUM('Table'[time]),ALLEXCEPT('Table','Table'[id_project]))
var b=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[id_project]))
return a/b
If you expected result is 5.3333... , please try calculated column as below:
Result =
var a=AVERAGEX(VALUES('Table'[id_project]),'Table'[time])
var b=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[id_project]))
return a/b
Best Regards,
Community Support Team _ Eason
Hi, @Anonymous
Not sure if the result of the record with id_project of 1011 is 4.8 or 5.33333...
If you expected result is 4.8 , please try calculated column as below:
Result_column =
var a=CALCULATE(SUM('Table'[time]),ALLEXCEPT('Table','Table'[id_project]))
var b=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[id_project]))
return a/b
If you expected result is 5.3333... , please try calculated column as below:
Result =
var a=AVERAGEX(VALUES('Table'[id_project]),'Table'[time])
var b=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[id_project]))
return a/b
Best Regards,
Community Support Team _ Eason
@Anonymous , Try a new measure like
averageX(values(Table[id_project]), divide(sum(Table[time]),count(Table[time])))
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |