Average of Two and Three Column

[AllanBerces]

Hi Good day,

Pls need assistance on how can i have the Overall output on my table base from average.

a. If the Fab column is 0 it will average by 3 three column (JC, Fab and Mat).

b. if the Fab column is Blank it will average by two column (JC and Mat).

Thank you

[Anonymous · Accepted solution]

@AllanBerces  This works.

[AllanBerces]

Hi @Anonymous  thank you, working perfectly.

[DataNinja777]

Hi @AllanBerces 

In addition to the anwer provided by @Ritaf1983 , I'd like to present you another way as there are multiple ways to achive your required output.  One way is convert your pivoted table to a long unpivoted table using Power Query's unpivot other column function which will supress blank rows as a default, and then you can write an averagex dax measure as shown below. 

I attach an example pbix file. 

Best regards,

[Ritaf1983]

Hi @AllanBerces 
You can use the formula :

Overall = IF(ISBLANK([FAB]) ||ISBLANK([JC]) || ISBLANK([MAT]),DIVIDE([MAT]+[JC]+[FAB],2),DIVIDE([MAT]+[JC]+[FAB],3))

pbix is attached

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[AllanBerces]

Hi @Ritaf1983  Thank you for you reply

i just notice theres something wrong on the column, the value of 2 instead of 72 and 94.

Thank you

[Ritaf1983]

There was a "writer error " .
' instead of +

The correct formula is :

Overall = IF(ISBLANK([FAB]) ||ISBLANK([JC]) || ISBLANK([MAT]),DIVIDE([MAT]+[JC]+[FAB],2),DIVIDE([MAT]+[JC]+[FAB],3))

 If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.