Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

Average between columns

Hello all

I need a help that maybe is simple but I have already watched several videos and forus and it does not work for me. I need to create a column that calculates the average of the 5 colunmas (Y1, Y2; Y3; Y4 and Y5) but only if they are non-zero.

How do I do that? or is it better to create measures than to create columns? The data comes as shown in the example and this new data I have I will use quite even then create new measures.

I really appreciate your help!!

DianisB_0-1657621134422.png

1 ACCEPTED SOLUTION
SolomonovAnton
Responsive Resident
Responsive Resident

hello 

 

I think it will help you:

 

calculation column = 

var _Y1 = if([Y1]=0,blank(),[Y1])

var _Y2 = if([Y2]=0,blank(),[Y2])
var _Y3 = if([Y3]=0,blank(),[Y3])
var _Y4 = if([Y4]=0,blank(),[Y4])

var _Y5 = if([Y5]=0,blank(),[Y5])

var _result = (_Y1+_Y2+_Y3+_Y4+_Y5)/(_Y1/_Y1+_Y2/_Y2+_Y3/_Y3+_Y4/_Y4+_Y5/_Y5)
return _result

 

View solution in original post

3 REPLIES 3
SolomonovAnton
Responsive Resident
Responsive Resident

hello 

 

I think it will help you:

 

calculation column = 

var _Y1 = if([Y1]=0,blank(),[Y1])

var _Y2 = if([Y2]=0,blank(),[Y2])
var _Y3 = if([Y3]=0,blank(),[Y3])
var _Y4 = if([Y4]=0,blank(),[Y4])

var _Y5 = if([Y5]=0,blank(),[Y5])

var _result = (_Y1+_Y2+_Y3+_Y4+_Y5)/(_Y1/_Y1+_Y2/_Y2+_Y3/_Y3+_Y4/_Y4+_Y5/_Y5)
return _result

 

Thank you very much for your help and I am glad that we can find the solutions by these means 🙂 ... then I also found a very similar one that worked:

Average =
where _Numerador = [Y1] + [Y2] + [Y3] + [Y4] + [Y5]
where _Denominador =
WHERE _1 = if (ISBLANK([Y1])||[Y1]=0, 0, 1)
WHERE _2 = if (ISBLANK([Y2])||[Y2]=0, 0, 1)
WHERE _3 = if (ISBLANK([Y3])||[Y3]=0, 0, 1)
where _4 = If (ISBLANK([Y4])||[Y4]=0, 0, 1)
where _5 = IF (ISBLANK([Y5])||[Y5]=0, 0, 1)
return
_1+_2+_3+_4+_5
return
IFERROR(_Numerador/_Denominador,0)

thank you for another solution. It's greate ! 

 

please mark topic as resolved 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors