Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic average dax

Hello,   I'm working on a dynamic dax to calculate the average duration as is shown in the picture.      So : for the 1st example, the first line has one data on duration stage 2 so the a...
Interesting Links
Need Help
Tips and Tricks
  • Pragati11's avatar
    Pragati11
    5 years ago

    Hi Anonymous ,

     

    In that case do something like this:

     

    CalcDAX = 

    var exp1 = COUNTX(Projects, Projects[Duration Stage 1])

    var exp2 = COUNTX(Projects, Projects[Duration Stage 2])

    var exp3 = COUNTX(Projects, Projects[Duration Stage 3])

    var exp4 = COUNTX(Projects, Projects[Duration Stage 4])

    var exp5 = exp1 + exp2 + exp3 + exp4

     

    RETURN

     

    DIVIDE(AVERAGE(Projects[Duration Stage 1])+AVERAGE(Projects[Duration Stage 2])+AVERAGE(Projects[Duration Stage 3])+AVERAGE(Projects[Duration Stage 4]), exp5, 0)

     

    See if the above DAX works.

     

    Thanks,

    Pragati

     

Pragati11's avatar
Pragati11
Super User
5 years ago

Hi Anonymous ,

 

In your DAX expression, rather than dividing by 4, you need to divide by summation of number of non-blank values in all of your 4 columns.

 

Can you attach a file with this sample data so that I can share a proper DAX calculation for this?

 

Thanks,

Pragati

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Pragati11 

Thank you for your return. Unfortunately i can't. But I can tell that all Duration stages are calculated columns and they are present in one table. 

Can you show me please how the dax is ?

 

Regards,

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    Hi Anonymous ,

     

    In that case do something like this:

     

    CalcDAX = 

    var exp1 = COUNTX(Projects, Projects[Duration Stage 1])

    var exp2 = COUNTX(Projects, Projects[Duration Stage 2])

    var exp3 = COUNTX(Projects, Projects[Duration Stage 3])

    var exp4 = COUNTX(Projects, Projects[Duration Stage 4])

    var exp5 = exp1 + exp2 + exp3 + exp4

     

    RETURN

     

    DIVIDE(AVERAGE(Projects[Duration Stage 1])+AVERAGE(Projects[Duration Stage 2])+AVERAGE(Projects[Duration Stage 3])+AVERAGE(Projects[Duration Stage 4]), exp5, 0)

     

    See if the above DAX works.

     

    Thanks,

    Pragati