Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

average divided by the count per project

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.

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a new measure like

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

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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