average divided by the count per project

[Anonymous]

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.

[v-easonf-msft · Accepted 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

[amitchandak]

@Anonymous , Try a new measure like

averageX(values(Table[id_project]), divide(sum(Table[time]),count(Table[time])))