GrandTotal to sumamry power pivot values

[ajocius1]

Hi, If I use average to display values in powerpivot table, can Grand total show total of those averages? So if my "Sale of product X" is on average 5 in Day1 column, 6 in Day2 Column, Grand total column would show 11? Normal query would use Average function in Grand Total column too..., but I need to summarice values in pivot.

I have added the file, where it is easier to see what I try to achieve. I show there pivot, that uses average also for Grand Total column and then I added yellow column with excel formulas, but I would like that to be part of pivot or powerpivot. Is it possible?

[JosephP]

add a measure to your pp pivot using this formula

=SUMX(SUMMARIZE(Data,Data[Day],"AvgMeasure", AVERAGE(Data[Sale])),[AvgMeasure])

[ajocius1]

Hi, tried new measure, but got following error message: "The expression is not valid or appears to be incomplete. Please review and correct the expression. The syntax for '[Day]' is incorrect."

[JosephP]

was that your actual file that you posted? it works there when I run it

[ajocius1]

yes, it was same file I tried to add measure. Would you be able to load file with measure saved in it?

[JosephP]

sure thing-here it is

[ajocius1]

This is great! It is just what was needed. I will now spend time to try to understand how this works

[JosephP]

you're welcome :-)

please don't forget to mark the thread solved (click the 'thread tools' link at the top, then 'mark solved')