8 Replies Latest reply on Nov 26, 2018 5:31 AM by sander.binda

# Weighted average in grand total.

Hello,

In the workbook im trying to compare the average price a customer paid for a 'article 1' in a certain week to the weighted average weekprice.

This works perfectly fine when the overview shows just 1 week, as soon as I add another week to the view the grandtotal  of the weighted average weekprice is just the average of 2,78+2,21.

I want the grandtotal of the weighted average weekprice to show 2,37 as well because 2,37 is the weighted average weekprice of both weeks combined (27599/11660=2.37)

Which in turn makes the grandtotal of the last column 100% (The endgoal)

If you filter to 1 week you see that this happends.

• ###### 1. Re: Weighted average in grand total.

Hi, Sander

Is it something like this? if yes, please find my solution attached.

Hope this helps

ZZ

• ###### 2. Re: Weighted average in grand total.

Hello Zhouyi,

Firstly thank you for your reply. This is indeed the direction I want to go to.

The formula does not work for me because in my original dataset 'Weighted average weekprice' is calculated. This results in me getting the error I can't use AVG because it is already an aggregration.

Then when I remove AVG from the formula im left with this:

IF SIZE() = 1 THEN

sum({ FIXED :SUM([Sales in €)}/{ FIXED :SUM([NettKgOut])})

ELSE

([Weighted average weekprice])

END

But this results in a grand total of €0,99. I've added a screenshot.

• ###### 3. Re: Weighted average in grand total.

Hi, Sander

Can you share a workbook with your weighted avg week price as calculation field to reflect your issue?

ZZ

• ###### 4. Re: Weighted average in grand total.

Hello,

I've added the calculated field to your workbook named 'calculation 2'. (No clue why I didn't added this in the first place)

• ###### 5. Re: Weighted average in grand total.

Hi, Sander

thanks for your calculation field, but I can't reproduce your issue by replacing the avg with calculation2 as shown below, the grand total is 2.37, not 0.99 as you mentioned

Can you double check?

ZZ

1 of 1 people found this helpful
• ###### 6. Re: Weighted average in grand total.

Hi ZZ,

I have found the issue, my dataset has many more articles and many more weeks in it, so I had to 'Article' and 'Loading week' to both Fixed expressions to return the right value.

Thank you for the help!

• ###### 7. Re: Weighted average in grand total.

No worries, and glad you figure it out

ZZ

• ###### 8. Re: Weighted average in grand total.

Hello ZZ,

Hope you still have time to help me with a new issue.

I've added an extra week now the grand total of 'Avg Wt' shows the grand total of the 3 weeks without the 3rd week being in the view (guess this happends due to the 'Fixed' part of the formula).

Is there a way to make sure it only shows the grand total of whatever is in the view? Because my original data set has 140 weeks and 60 articles so I need to make sure the grand total applies only to what is in the view.