Calculate values - in an array?

[tourgis2000]

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

 

[Jacob Barnett]

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'):

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

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

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:

Buy Count:= COUNTA(Fact[Item])

Position Sum:= SUM(Fact[Position])

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

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

Thanks again,

Martin

 

[Jacob Barnett]

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:

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

I get it now.

Thank you,

Martin