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 average is gonna be: 750 

for the 2nd example the 10th line has 2 data in duration stage 1: 504 and duration stage 2: 286 so the average is gonna be 504+286/2 = 395 

for the 3rd example the line 18th has 2 data in duration stage 2: 184 and duration stage 3: 1340 so the average is gonna be 184+13402 = 762 

for the 4th example the line 24th has 3 data in duration stage 1: 1018 and duration stage 2 : 0 and duration stage 3: 3652 so the average is gonna be 1018+0+3652 / 3 = 1556.66

and if we have data for all duration stages the average is gonna be: duration stage 1 + duration stage 2 + duration stage 3 + duration stage 4 /4

 

the formula that I'm using is : 

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

 

Do you have any tips or ideas? 

Thank you very much

Regards,

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

     

3 Replies

  • 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