Pivot table sum

[#1]

Hi,

I'm trying to make a table showing the amount of money we save for our customer, by showing the amount of money they spent on a lane (from a certain city to another city)  in 2012, and what they are spending now we are arranging their transports.

So the table has the following content:

Average paid rate 2012: what the customer paid in 2012, before we arranged their transport

Average freight spend SWL: what we the customer paid since we arranged their transports

# of loads: number of loads we shipped for them on the specific lane.

My data comes from two datasets; one set with all the shipment data from my company, and one file with the 2012 rates of the customer. I have Flagged our own data (because there are also many 2012 lanes in the file which are not relevant anymore), and made the connection between the files based on "Lane".

In the outer right column, I want to show what the customer would have paid on these lanes if they would have arranged their transports themselves. I do this, by multiplying their rate, with the number of loads shipped. Where there is no data

=if(sum({<FlagA={'A'}>}TotalFreight)=0

,0,Avg({<FlagA={'A'}>}[Rates 2012 in Eur])*

count({<FlagA={'A'}={0}>}TMWO))

As you can see in the picture, this formula works perfectly for the separate lanes, but my totals get all messed up. Can anybody tell me, how I can get the same numbers per lane, but the correct total? (=3144+3110,40+900+294=7418,4)

Many thanks!

[Gysbert_Wassenaar]

Perhaps an extra aggregation over the dimensions will help:

=sum(aggr(

if(sum({<FlagA={'A'}>}TotalFreight)=0

,0,Avg({<FlagA={'A'}>}[Rates 2012 in Eur])*

count({<FlagA={'A'}={0}>}TMWO))

, ConsigneeCount...something... , Lane)

[PrashantSangle · Accepted solution]

Hi,

If you are using straight table .

go to chart Properties->Expression->select Expression->Total Mode->select (Sum) of Rows.

Regards,

[#4]

Yeah, that worked. Thank you Max!

[PrashantSangle]

You Welcome,

Regards,