Dynamic average dax

[Anonymous]

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,

[Pragati11]

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]

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 · Accepted solution]

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