Show correct average values in the "total" row

[Anonymous]

Hello!

Target:

I need to show average values in the "total" row at the bottom by each column.

Description:

I've got a pivot table with two dimensions (one of them are calculated). I selected a "show partial sums" option to my calculated dimension so the "total" row appeared but the values were strange. How to show average values?  Screenshot is attached.

Dimensions:

1. Week Row = aggr(if( min(Date) <= Date(WeekEnd(min(Date))), Date(WeekStart(min(Date))) & '-' & Date(WeekEnd(min(Date))),  ClientID)

2. Week Column = Week

Expression:

sum(Sales) / sum( TOTAL <ClientID> Sales)

P.s. I can't do any change in the load script and change places for dimensions.

Best wishes,

Evgeniy

[zhadrakas]

maybe this:

AVG(TOTAL <ClientID> Sales)

your probably Need to replace ClientID with your dimensions

[Anonymous]

Thanks for the response.

The wrong values in the total row appeared because they are based on the second dimension in a chart - Week

The expression: aggr( sum(Sales) / sum( TOTAL <ClientID> Sales) ,Week)  - give the same wrong "total" values in cells.

your probably Need to replace ClientID with your dimensions

What do you mean it this case? How would it look like?

[zhadrakas]

maybe

AVG(TOTAL <Week, ClientID> Sales)  or

aggr(AVG(TOTAL <ClientID> Sales) , Week)

it would be great if you share a sample qvw that we can test that.

[Anonymous]

Hello, Tim Driller

These expressions didn't solve the problem (and they also changed a calculation way what's incorrect)

A sample qvw is attached.

But as a result I need to get a correct expression/tip (but not a qvw document).

Best wishes,

Evgeniy

[zhadrakas]

Hi,

i don't find the right Expression for your case.

i think your expected results should look like this?

Total		3,90%	17,90%	20,80%	22,00%	15,10%	20,40%
			23,80%	15,90%	13,95%	12,00%	18,90%

With following Expression you can force another Expression to Display in total row.

If(RowNo()=0
,'TOTAL ROW EXPRESSION'
,sum(Sales) / sum( TOTAL <ClientID> Sales)
)

[Anonymous]

I expected results should look like:

30.9 % ,  23.80 %,   25.86 %, etc.

But OK. Thank you for your attention!

Best wishes,

Evgeniy