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 -

 

[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 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]

I'm not following you.

Maybe you could share a copy on Dropbox...

 

[nikkollai]

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

 

[GDRIII]

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]

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

[TABLE="width: 246"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]PRODUCT[/TD]
[TD]COST[/TD]
[TD]AMT[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]TTL COST[/TD]
[TD="align: right"]$5,555[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]TTL COST[/TD]
[TD="align: right"]$6,586[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]TTL COST[/TD]
[TD="align: right"]$7,777[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]COST1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]COST1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]COST1[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]COST2[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]COST2[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]COST2[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]COST3[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]COST3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]COST3[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[nikkollai]

Thank you very much
N -