# Pull AVERAGE value from the same row across multiple columns. Very simple in regular Excel , Nightmare for me in Powerpivot



## nikkollai (Jan 20, 2015)

Hello Powerpivoters, 

(Wow! Powerpivoters - could be a domain name or something... www.powerpivitors.com ... not bad) 

Anyways, i am myself a baby powerpivitor and it seems I have hit a roadblock with a simple task - pull AVERAGE value from the same row across multiple columns. 

How would i get an average for e.i product AAA from columns D,E and F

In regular excel it's easy : =AVERAGE(D2:F2), but how you do it in powerpivot?

*** same applies to 


A
B
C
D
E
F
1
PRODUCT
TTL COST
COST1
COST2
COST3
2
AAA
$5555$20
$21
$22
3
BBB
$6586
$15
$11
$18
4


<tbody>

</tbody>
Thank you very much for your help
Nick -


----------



## trankillity (Jan 21, 2015)

I'm also new to PowerPivot but from what I understand, you're kinda missing the point of pivots in general. The point is that for stuff like that, you don't need to arrange those fields horizontally - moreso, each of those should be pivoted fields, in the following layout:


*PRODUCT**TYPE**VALUE*AAATTL COST$5,555AAACOST1$20AAACOST2$21AAACOST3$22BBBTTL COST$6,586BBBCOST1$15BBBCOST2$11BBBCOST3$18

<tbody>

</tbody>
That way, you can more easily filter and aggregate based on the different fields. Then you just drag the "VALUE" column into the Values field on the pivot table and you can sum, average, count and all the other aggregations.

As far as I can tell, you should think of the PRODUCT as the key with each of the TYPE fields as a property of each PRODUCT and the VALUE field as the variables for each property of each product. That's the most basic level of the idea.


----------



## nikkollai (Jan 21, 2015)

Thank you very much *trankillity* for your answer

Unfortunately, the database i was given is already built the way it's shown in my post.  i am out of options here. 

Tks again
Nick


----------



## trankillity (Jan 21, 2015)

You're never out of options! 

How are you importing the data from your DB into PowerPivot? If it's via SQL/query, you should investigate the PIVOT functionality of SQL Server.


----------



## GDRIII (Jan 21, 2015)

I imagine Scott will have a better way but, a calculated column averaging the row data would seem pretty simple.


----------



## nikkollai (Jan 21, 2015)

trankillity said:


> You're never out of options!
> 
> How are you importing the data from your DB into PowerPivot? If it's via SQL/query, you should investigate the PIVOT functionality of SQL Server.



Hi again
Unfortunately, it's not a SQL Server it's a .dbf file that stores the data.  I do think these is something i could do to change the database structure. 
i am very surprised there is not easy way to solve this issue (as shown in my table) , again it's a piece of cake in regular excel. 
Same problem applies not only to averaging across the columns but also to finding MIN/MAX values. 

GDRIII: Could you please give me an example of your idea below

\\
calculated column averaging the row data would seem pretty simple
//




Nick -


----------



## GDRIII (Jan 22, 2015)

On the left side of the table in the PowerPivot window insert a column by clicking Add Column.

Name the column Costing Average and type a formula like =([D]+[E]+[F])/3

Then you could add a measure [Average Cost]:=AVERAGE(table[Average Cost])

From there you could do something like [Average Cost]:=CALCULATE([Average Cost],table[Product]="AAA") to hard code AAA's Average Cost

Calculated Columns do slow down performance but, depending on the size of your model, you might not be measurably hindered.

Hope this helps


----------



## nikkollai (Jan 22, 2015)

GDRIII said:


> On the left side of the table in the PowerPivot window insert a column by clicking Add Column.
> 
> Name the column Costing Average and type a formula like =([D]+[E]+[F])/3
> 
> ...




Hi , 

That's very good idea except i forgot to mention that  some of the records  (about 30%) have null or zero values (see below) and dividing them by 3 would mess things up because we are not sure if all three records contain data. 
I think first i would need to create one column for IF() condition to pinpoint which cells are  ">0"  and give them "1" value then COUNT() all the values and then use that number (1,2 or 3) =([D]+[E]+[F])/ (1,2 or 3). 

This is a very long and painful way for such a simple task. I wish there was a much simpler solution. 



ABCDEF1PRODUCTTTL COSTCOST1COST2COST32AAA$55550.00$21$223BBB$65860.00$11      null4

<tbody>

</tbody>

Nick -


----------



## GDRIII (Jan 22, 2015)

How about a calculated column for each to check for BLANK for D, E, and F...three columns.  Then a 4th column to sum them...then use that column as your devisor in your fifth column.

I know there's a better way but, I'm stuck thinking this way right now...

DIVIDE is "A safe divide function with ability to handle divide by zero cases" or something like that but, I haven't played with it.


----------



## nikkollai (Jan 22, 2015)

GDRIII said:


> How about a calculated column for each to check for BLANK for D, E, and F...three columns.  Then a 4th column to sum them...then use that column as your devisor in your fifth column.
> 
> I know there's a better way but, I'm stuck thinking this way right now...
> 
> DIVIDE is "A safe divide function with ability to handle divide by zero cases" or something like that but, I haven't played with it.



Hi, 

It works in powerpivot , the fourth column is showing correct data. I tried with both DIVIDE and without and get the same result. However, when i bring the fourth column into excel i get a "column sum all number"  i went to the filed setting and tried to use Max or Min and get an error instead. 
Something is wrong with iteration. Any ideas why i see correct values in powerpivot but total column summed up value in excel? 

N -


----------



## nikkollai (Jan 20, 2015)

Hello Powerpivoters, 

(Wow! Powerpivoters - could be a domain name or something... www.powerpivitors.com ... not bad) 

Anyways, i am myself a baby powerpivitor and it seems I have hit a roadblock with a simple task - pull AVERAGE value from the same row across multiple columns. 

How would i get an average for e.i product AAA from columns D,E and F

In regular excel it's easy : =AVERAGE(D2:F2), but how you do it in powerpivot?

*** same applies to 


A
B
C
D
E
F
1
PRODUCT
TTL COST
COST1
COST2
COST3
2
AAA
$5555$20
$21
$22
3
BBB
$6586
$15
$11
$18
4


<tbody>

</tbody>
Thank you very much for your help
Nick -


----------



## nikkollai (Jan 22, 2015)

GDRIII said:


> How about a calculated column for each to check for BLANK for D, E, and F...three columns.  Then a 4th column to sum them...then use that column as your devisor in your fifth column.
> 
> I know there's a better way but, I'm stuck thinking this way right now...
> 
> DIVIDE is "A safe divide function with ability to handle divide by zero cases" or something like that but, I haven't played with it.



Hi GDRIII, 

I just followed your instructions. I am getting the average value both with DIVIDE and simple arithmetic in the fifth column but when transferred to the excel  i am getting total summed up value of the total column for each row.  For some reason iteration does not work. Any idea why? 

Tks, Nick -


----------



## GDRIII (Jan 23, 2015)

I'm not following you.

Maybe you could share a copy on Dropbox...


----------



## nikkollai (Jan 24, 2015)

*GDRIII*, 
It just started to work, so issue resolved. 
Still i wish there was a solution with less pre work. 
Tks, Nick  -


----------



## GDRIII (Jan 26, 2015)

That's just the learning curve.  Once you figure out syntax and formulas, you won't struggle so much.

Bright side, it's done, it works, the formula's are portable to any other model with the same structure, you learned a bunch.

Now you could plug in some disconnected harvest measures and look at user imputed costing increases, discounts against price to see how your margins change, look at calendar comaprizon data like no other, the sky is the limit.

Then you'd no longer just have a Pivot table, you'd have an interactive reporting application that will make heads spin.

Glad


----------



## PowerDAX (Jan 26, 2015)

You would typically want to stray away from extra calculated columns where they are not needed.  You can perform this as a calculated field to allow it to calculate the average when sliced and diced by different dimensions appropriately (you do not want to get an average or sum of averages).  The following calculated field can be created:  

Average Cost:=DIVIDE( SUMX( Table1, Table1[COST1]+Table1[COST2]+Table1[COST3]), SUMX( Table1, IF( Table1[COST1]>0,1,0) + IF( Table1[COST2]>0,1,0) + IF( Table1[COST3]>0,1,0) ) )

You want to utilize DIVIDE so you do not run into divide by zero issues and SUMX so that you iterate each row in order to calculate the average correctly.

As pointed out earlier, you would also want to pivot this data if possible.....even if you open the data with Excel, you can then utilize Power Query to pivot the data into a cleaner format (less cardinality in a single column vs the four columns you point out) and ....then utilize a calculated field 


PRODUCTCOSTAMTAAATTL COST$5,555BBBTTL COST$6,586CCCTTL COST$7,777AAACOST10BBBCOST10CCCCOST130AAACOST221BBBCOST211CCCCOST233AAACOST322BBBCOST3 CCCCOST350

<colgroup><col><col><col></colgroup><tbody>

</tbody>
Average Cost:=CALCULATE(AVERAGE([AMT]),FILTER(Table2, [COST]<>"TTL COST" && [AMT]<>0))


----------



## nikkollai (Jan 28, 2015)

Thank you very much 
N -


----------

