how to correctly display totals which are contained in the datasource

[#1]

I have a source table which looks somehow like this

region	status	Data
North	on	100
North	off	200
West	on	50
West	off	300
South	on	400
South	off	210
TOT	on	520
TOT	off	490

As you can see my data is split by regions and in the data table itself I have the totals.

This because data is somehow overlapping between regions, so if I would exclude the TOT entries, I would come up with wrong total values.I could create four different charts with set analysis, but for real estate space issues, I would like to have this info in one chart.

If I create this single chart with <region> and <status> as dimensions and sum(data) as expression, I have the problem that if the user does not select anything in <region>, the data which is shown is highly irrealistic.

Is there a way to have a sort of set analysis, which is setting the <region> dimension to TOT, but still let users select either North, West or South?

thanks!

[ashfaq_haseeb]

Hi,

How about using trellis chart like attached.

Regards

ASHFAQ

[#3]

Erik

can you have to sets of data like : TOT and other regions (N, S, W)

so you load table in 2 times

LOAD

TOT     as HeadRegion

*

From    where reigon='TOT

LOAD

Region as HeadRegion,

*

From ... wHERE region <>'TOT

and then you can either add a dimension (but still wrong big totals) or let the user select headregion

best regards

chris

[#4]

that is actually a very nice solution!

but I am having actually four statuses per region and it is a wish to have a waterfall chart (and I achieved that with different expressions and playing with the bar offset of each expression)

Pardon my ignorance, but how do you create a trellis chart? So that I can see if my  waterfall chart would look fine in it?

[#5 · Accepted solution]

example attached

[ashfaq_haseeb]

Hi,

Look at the attached image.

Go to chart propertied --> Dimension --> Click on trellis -->enable trellis

Regards

ASHFAQ

[#7]

I really love the outlook of the Trellis chart and potentially works.

As regions though have very different numbers and trellis share the same scale, it is not really feasible for this dataset.

But indeed I will keep it in mind for future reference, thank you Mohammed!

Felim answer is the most applicable to this issue.

thank you both