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

[nikkollai]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]PRODUCT
[/TD]
[TD]TTL COST
[/TD]
[TD]COST1
[/TD]
[TD]COST2
[/TD]
[TD]COST3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]AAA
[/TD]
[TD]$5555[/TD]
[TD]$20
[/TD]
[TD]$21
[/TD]
[TD]$22
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]BBB
[/TD]
[TD]$6586
[/TD]
[TD]$15
[/TD]
[TD]$11
[/TD]
[TD]$18
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for your help
Nick -

 

[trankillity]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]PRODUCT[/TD]
[TD]TYPE[/TD]
[TD]VALUE[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]TTL COST[/TD]
[TD]$5,555[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]COST1[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]COST2[/TD]
[TD]$21[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]COST3[/TD]
[TD]$22[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]TTL COST[/TD]
[TD]$6,586[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]COST1[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]COST2[/TD]
[TD]$11[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]COST3[/TD]
[TD]$18[/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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]

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

 

[nikkollai]

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]

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]

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

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.


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PRODUCT[/TD]
[TD]TTL COST[/TD]
[TD]COST1[/TD]
[TD]COST2[/TD]
[TD]COST3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AAA[/TD]
[TD]$5555[/TD]
[TD]0.00[/TD]
[TD]$21[/TD]
[TD]$22[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BBB[/TD]
[TD]$6586[/TD]
[TD]0.00[/TD]
[TD]$11[/TD]
[TD] null[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Nick -

 

[GDRIII]

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]

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 -