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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sbharti123
Frequent Visitor

Aggregating columns in each row having null values

Hi All,

 

I need a small help. I have a table that looks like below:

 

NAMEID1ID2ID3
A1null9
B235
C3null5

 

Now, I need to create a calculated column, which is average of all id columns in each row, something like below:

 

NAMEID1ID2ID3AVG
A1null95
B2353.33
C3null54

 

Is this possible to do in power bi, as I can did replace nulls with 0, but in that case my average calculations were wrong. I would be grateful if someone could guide me.

 

Thanks

 

1 ACCEPTED SOLUTION

Thanks for your quick guide. I managed to get it right as below:

 

Avrg =
VAR summ = (table1[id1]+ table1[id2] + table1[id3])
VAR cnt = CALCULATE(COUNTAX(table1,[id1]))+ CALCULATE(COUNTAX (table1,[id2])) + CALCULATE(COUNTAX(table1,[id3]))
return DIVIDE(summ,cnt)

View solution in original post

2 REPLIES 2
jthomson
Solution Sage
Solution Sage

The best option would be to do some manipulation of your data so you just have name, id number and value as columns and then use a matrix, but a simple sum of each column divided by a counta of each column ought to work as well

Thanks for your quick guide. I managed to get it right as below:

 

Avrg =
VAR summ = (table1[id1]+ table1[id2] + table1[id3])
VAR cnt = CALCULATE(COUNTAX(table1,[id1]))+ CALCULATE(COUNTAX (table1,[id2])) + CALCULATE(COUNTAX(table1,[id3]))
return DIVIDE(summ,cnt)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.