Distinct Average

[zaforir2002]

Hi,

I am using Excel PowerPivot from the tabular data model and need to do average on distinct records, although I have gone trough with many suggestions are available online, but not getting the correct output. 

Sample table

prod	qty
Apple	10
Apple	15
Apple	10
Organe	5
Organe	12
Organe	12
Organe	12
Banana	8
Banana	8

Result I am getting

prod	my_avg_qty_return
Apple	10.22222
Orange	10.22222
Banana	10.22222

Result I am expecting

prod	avg_qty_expected
Apple	11.66667
Orange	10.25
Banana	8

[Datatouille · Accepted solution]

Hi @zaforir2002

I think you have a confusion between a calculated column and a measure.

If you create the DAX measure:

AvgQty = Average(YourTable[Qty] ) and bring in this measure in a pivot table, you'll get the correct result thanks to filter context.

[zaforir2002]

Thanks, @Datatouille,

I have used it in a measure, not in the calculated column, but the results are same for each prod :(.

[Datatouille]

Do you have everything in the same table or do you bring in products from another table ?

In that case, you need to create a (1 to Many) Relationship between Products and your Quantity Table.

[zaforir2002]

All is from same table @Datatouille

[Datatouille]

Will you please share the file ? because it should work !