# Calculate values - in an array?

#### tourgis2000

##### New Member
Hi all,

I'm a beginner to PowerPivot, and I don't really know how to ask my question, so any help would be greatly appreciated.

I have a table of values with two columns:

Column A = Items
Column B = Position of Item (values of about 1-15 but could be greater)

I want to produce a measure that (1) calculates the number of Items by position and (2) multiplies that result against the position of the item. An example might be:

 Item Position Apples 1 Apples 3 Apples 1 Pears 2 Oranges 6 Oranges 6

<tbody>
</tbody>

The result I'm trying to get to is:

 1 2 3 4 5 6 Total Apples 2 1 Pears 1 Oranges 2 Total 2 1 1 0 0 2 =((2x1)+(1x2)+(1x3)+(0x4)+(0x5)+(2x6))/6

<tbody>
</tbody>

I can create this in an ordinary pivot and then use calculations to get the answer but how can I do all of it in a PowerPivot measure?

Thanks,

Martin

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Jacob Barnett

##### Board Regular
Martin, welcome to PowerPivot!

The good news is that this is possible, however its hardly a straightforward introduction to DAX!

I started with a measure to do a basic count of the items (assuming your table is called 'Fact'):

Code:
``[Count Items]=COUNTA(Fact[Item])``

The main measure then tests to see if the Item has a single value and if so returns [Count Items], if not it runs an interative average function whereby it calculates the Total Count * Position for each position and then averages them.

Code:
``````=IF(HASONEVALUE(Fact[Item]),[Count Items],
AVERAGEX(VALUES(Fact[Position]), MAX(Fact[Position])*CALCULATE([Count Items],ALL(Fact[Item])))
)``````

One potential complication is where not all of the 'Positions' exist in the table - e.g. your sample data had nothing in 4 or 5 - this would be a problem not only in terms of giving you all the column headers in your pivot but also in terms of the average. If this were to be the case in your real data then you would need to use a separate 'Position' table - in a much bigger data set its probably no issue.

Hope this helps!

Jacob

#### tourgis2000

##### New Member
Hi Jacob,

Thanks for taking the time to look at my problem.

I've tried to implement it but I don't get the right result - very possibly because I don't understand it and I'm not using the right fields. What I managed to do in the meantime was this:

Code:
``Buy Count:= COUNTA(Fact[Item])``

Code:
``Position Sum:= SUM(Fact[Position])``

Code:
``Position Calculation:= [Position Sum]/[Buy Count]``

It seems to work, but I'll spend some time with your method.

Thanks again,

Martin

#### Jacob Barnett

##### Board Regular
I was being too literal, my formula does exactly what you had indicated a bit like an array formula although mathematically your simpler version achieves the same!

For some reason the denominator for the second half of the formula was cut off!! Should have been:

Code:
``````=IF(HASONEVALUE(Fact[Item]),[Count Items],
AVERAGEX(VALUES(Fact[Position]), MAX(Fact[Position])*CALCULATE([Count Items],ALL(Fact[Item]))) /
CALCULATE(COUNTA(Fact[Item]), ALL(Fact[Item]))
)``````

Jacob

#### tourgis2000

##### New Member
I get it now.

Thank you,

Martin

Replies
1
Views
276
Replies
9
Views
212
Replies
1
Views
704
Replies
6
Views
477
Replies
0
Views
246

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,559
Messages
5,832,498
Members
430,136
Latest member
Asir Jefferson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back