Forum Discussion
Distinct Average
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 |
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.
5 Replies
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.
Thanks, Datatouille,
I have used it in a measure, not in the calculated column, but the results are same for each prod :(.
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.
