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.
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])))
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 |
---|---|
104 | |
104 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |