Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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])))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
88 | |
85 | |
68 | |
66 | |
64 |
User | Count |
---|---|
208 | |
120 | |
113 | |
79 | |
72 |