To show a static total in a chart

[josephinetedesc]

hi

what I want is to allow users to select car type = Fords, but the total not to change ie to be static at 52 for example for the month of Jan.  At present I can get the total but if the users select Fords -it shows the Fords total, I want to see the Fords total in relation to the Total of all cars sold.

Thank you

Jo

Month	1/01/2015	1/02/2015	1/03/2015	1/04/2015
Fords	30	40	30	40
Holdens	20	30	20	30
Kias	2	5	6	4
total	52	75	56	74

[MK_QSL]

Sample Data:

Load * Inline

[

  Car, Month, Sales

  FORD, Jan, 100

  FORD, Feb, 120

  FORD, Mar, 90

  HOLDENS, Jan, 120

  HOLDENS, Feb, 80

  HOLDENS, Mar, 110

  KIAS, Jan, 120

  KIAS, Feb, 70

  KIAS, Mar, 100

];

Create a Pivot Table

Dimension

Car

Month

Expression

IF(Dimensionality() = 0, Sum({1}Sales), SUM(Sales))

[fred_s]

Hi Josephine,

Check the attachment.

It's a different approach, but maybe you like it.

Grtz Fred

[josephinetedesc]

Thank you Mannish  I will need to check out dimensionality.

[josephinetedesc]

Thank you Fred

I am at home so I have a limit to the number of files I can open (5???).  At work tomorrow I will look at the file to see the set analysis used.

[josephinetedesc]

Am I correct in the first instance the Dimension is Sales?

[josephinetedesc]

Also Fred the gauge would not work for me - because the improtat thing is to see how well each carmake is perorming againsst the total of all cars sold.  Cannot wait to getto work tomorrow 😕

[josephinetedesc]

Sum({1} [NumSold])

This seems to work ... will there be a problem with this formula??  I changed the figures ...could not wait to get to work- curiosity was driving me insane

[MK_QSL]

Provide sample data in excel file along with the result you are looking for.

[josephinetedesc]

done ... Thanks for your help - obviously done late at night when not thinking clearly!!!!