en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Ole  
#1 Posted : Friday, November 6, 2015 9:01:13 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 324

This is a Tip on how to create a bar chart where a measure is shown as bars while a goal is shown as a horizontal line on each bar. It is not as simple as a combined bar chart / line chart as this would result in connected lines and non-horizontal lines.

Instead, we are going to use a stacked bar chart where we carefully insert the goal as a thin stacked bar (giving the impression of a line) between a lower part and an upper part of the measure.

The example given here, is looking at two years of data where we would like to express current year’s values as bars and last year’s values as a horizontal goal line.

Basis crosstab: No of Sales per Months by Last Year and This Year:

UserPostedImage

To do this we will need to work with a stacked bar chart with a basis design, in this example, of No of Sales (measure) together with months on one axis and the two years on the other axis.

Furthermore, the actual visible stacked bars are made up from four calculated columns:


LowerMeasureBar: if A:(sum(d1, 0, m1)) < B:(sum(d2, 0, m1)) then (A - sum(c3, 0, m1)) else B

The LowerMeasureBar will create the lower part of the blue bar in the screenshot below.


WhiteBar: if A:(sum(d1, 0, m1)) < B:(sum(d2, 0, m1)) then 0 else (A - B - sum(c3, 0, m1))

The WhiteBar will add a white (invisible) portion to the stacked bar chart in case the Goal (last year) is higher than the Measure (this year).


GoalBar: max(d2, all, m1) * 0,02

I am using this syntax to control the thickness of the Goal bar – in this case 2% of the highest bar. This relative approach makes sure that the solution is scalable. You can change the percentage to e.g. 1% or 5% to make the line appear thinner or thicker.


UpperMeasureBar: if A:(sum(d1, 0, m1)) < B:(sum(d2, 0, m1)) then (B - sum(c3, 0, m1) - sum(c2, 0, m1) - sum(c1, 0, m1)) else 0

This calculation adds the upper part of the Measure bar (if anything left to add) to complete the stacked bar chart.


Finally, the Visibility property has been applied to hide the original measure. In this way the stacked bar chart will based on the four calculated columns only.

Notice: Do not expect labels or legends to work well with this solution. In the screen shot I have enabled the legend, and to show the texts you see there, I have renamed my calculation UpperMeasureBar to “This Year”; GoalBar has been renamed to “Last Year”; WhiteBar has been renamed to “ “; and LowerMeasureBar has been renamed to “ “.

The resulting bar chart with horizontal goal lines:

UserPostedImage

Edited by user Friday, November 6, 2015 9:04:36 AM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of TARGIT A/S.

UserPostedImage Ole Dyring
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error