Home / Business Intelligence / Two Advantages of a Manual Maximum Calculation in Xcelsius

Two Advantages of a Manual Maximum Calculation in Xcelsius

Problem A: I have data that could be in the 1000s but sometimes could be 1 or 2.  Users do not want to see a division of 1.5 (or any part of a whole) since the data is tracking incidents and there are never “half incidents.”  How do I force the values to integers without losing the divisors for the high numbers (i.e. when there is 1000 maximum, I want to still see 250, 500, 750 divisors)?


Solution: Set a manual calculated maximum

{ =IF(MAX(AE15:AE26)<100,100,MAX(AE15:AE26)) } and then set the division number to 1.  With the division number to 1, it often also looks better to remove the grid lines (in case the data for the charts sometimes is much higher).


  Problem B:  I want to display a line chart on top of a stacked bar chart because there is no combination chart with this option.  My data for the stacked bar chart, however, is sometimes over 100 and sometimes under 100 and the extra space shifts the chart so that the two do not align.  How can I make the layered charts always align? 


 (Layered line chart does not line up with stacked bar chart when maximum is less than 100)


 (Layered line chart lines up with stacked bar chart when the maximum is over 100)


Note: If the charts are less than a magnitude different, the following method is a not-so-elegant-but-perhaps-acceptable workaround.  In the example above, I have emphasized the issue of alignment by making the different much greater in which case a more-involved display control of multiple charts may be the only acceptable option. 

Solution: Set a manual calculated maximum within an if() statement so that the maximum is always 100 or greater even if the stacked bar chart total is less than 100. (See above note for exception) 


This “fix” may not be acceptable for the right visual consumption. What may need to be done when the data is more than a magnitude of 10 different is to work with display properties for two charts with different widths based upon the maximum chart height formula instead.

About Amy

Amy is a Senior Technical Consultant at InfoSol. In one form or another, she has been working with data for 30 years. Amy’s education and early work experience in Analytical Chemistry led to a passion in improving reporting out of Microsoft Excel-based and Access-based tools. Frustrated with the monotony of “cranking out samples,” she studied and trained to get her MCSE with Microsoft in order to follow her IT-focused passions. Amy has been with InfoSol for 19 years now. Certified as a BusinessObjects Certified Professional, she can often be found providing client installations and migrations, as well as data integration, universe, report and dashboard design. She still says her greatest passion is for process improvement in whatever form it may take.

Check Also

InfoSol Blog Tech Tip

Tech Tip: How to refresh Tableau Hyper extracts based on an event

Tableau uses the concept of hyper extracts – a compressed snapshot of data stored on ...