Displaying Sum or Total on top of a Stacked Column Chart in Webi

[mhmohammed]

I've used the below 2 workarounds a few times, never thought of writing a blog, until lawilt#overview posted this question.

The sample data has 3 objects, Year, Month, and Sales Revenue $K. We create a variable (measure) called Sales Revenue $K by Year as =Sum([Sales Revenue $K] In ([Year])).

As I said, there are 2 ways to achieve this:

1. Stacked Column Chart on top of Line Chart

Line Chart

Line Chart, placed in the background, with everything in Chart formatted using White color or made Transparent (including Background), except the Data Values of points on the Line.

• Year, and Sales Revenue $K by Year are used. Image below shows selected Chart Type, and Assign Data details.

• Result looks like the image below

• Right Click on Line Chart -> Order -> click Send to Back


• Updates to be made in the Format Chart window of Line Chart
  
  
  
  • Global -> General -> Name -> Chart in the background
  
  
  • Global -> General -> Width = 16cm and Height = 8cm
  
  
  • Global -> Data Values -> Check the box for Data Label Displaying Mode
  
  
  • Global -> Data Values -> Data Label Displaying Mode -> Orientation -> Horizontal
  
  
  • Global -> Palette and Style -> Marker -> Uncheck the box for Line Symbol
  
  
  • Global -> Palette and Style -> Chart Series Style -> Line Effects -> Line Width -> 0
  
  
  • Global -> Background -> Opacity 0% (to make the background transparent)
  
  
  • Legend -> Design -> General -> Legend -> Uncheck the box for Visible
  
  
  • Category Axis -> Design -> Color Options -> Axis Color Opacity 0%, Grid Color Opacity 0%, and Grid Background Color Opacity 0%
  
  
  • Category Axis -> Design -> Text -> Font -> White color
  
  
  • Category Axis -> Title -> Text -> Font -> White color
  
  
  • Category Axis -> Title -> Border and Background -> Border Color Opacity 0% and Background Color Opacity 0%
  
  
  • Value Axis -> Design -> Color Options -> Axis Color Opacity 0%
  
  
  • Value Axis -> Design -> Text -> Font -> White color
  
  
  • Value Axis -> Title -> Text -> Font -> White color
  
  
  • Value Axis -> Title -> Border and Background -> Border Color Opacity 0% and Background Color Opacity 0%
  
  
  • Plot Area -> Background -> Category Axis Grid Color Opacity 0%
  
  
  • Click Apply, OK, and the result looks like the below image. It's displaying the Sum/Total values, which is exactly what we need.
  
  
  
  

Stacked Column Chart

Stacked Column Chart, placed in the front (or on top), with Transparent Background

• Year, Month, and Sales Revenue $K are used. Image below shows selected Chart Type, and Assign Data details.

• Result looks like the image below

• Right Click on Stacked Column Chart -> Order -> click Bring to Front


• Updates to be made in the Format Chart window of Stacked Column Chart
  
  
  
  • Global -> Layout -> Relative Positioning -> Horizontal -> 0cm from Left edge of Chart in the background (Line Chart) - This is the 1st step to overlap both the charts.
  
  
  • Global -> Layout -> Relative Positioning -> Vertical -> 1cm from Top edge of Chart in the background (Line Chart) - This is the 2nd step to overlap both the charts.
  
  
  • Global -> General -> Name -> Chart in the front
  
  
  • Global -> General -> Width = 16cm and Height = 8cm
  
  
  • Global -> Background -> Opacity 0% (to make the background transparent)
  
  
  • Legend -> Design -> Layout -> Location -> Bottom
  
  
  • Legend ->Title -> General -> Title -> Uncheck the box for Visible
  
  
  • Category Axis -> Title -> General -> Title -> Uncheck the box for Visible
  
  
  • Click Apply, OK and the result looks like the image below, which is the final result we're looking for. Voila!
  
  
  
  

In the below pic, you see that the data value 8,096 is behind grid line, to fix that, you'll have to make Opacity 0%, on the Grid line. Voila!

2. Combined Column Line Chart with 2 Y-Axes

• Year, Month, Sales Revenue $K, and Sales Revenue $K by Year are used. Image below shows selected Chart type, and Assign Data details.

 

I'll add the steps for 2nd workaround in a couple of weeks. But I guess, most of you got the idea, as it's not much different from the 1st workaround. Try it, and post your questions if you're stuck.

Please let me know if you find any typos or grammatical errors.

Feedback is welcome!

Good luck!

[arvins]

Dear M Mohammed,

I tried first variant - it is clear, but problem is that values of line chart positioned not precisely above columns - I believe column width and position of values of chart line bullets are out of sync

[mhmohammed]

Hi Arvins,

I see that the Amounts are different, so I wonder if you're using the same measure in both (column & line) value axes (1 & 2).

Step 1: Make sure you're using the same measure in both value axis (1 & 2)

Step 2: Right click and go to Format Chart -> Global -> Data Values -> Check Data Label Displaying Mode -> Scroll down and select Orientation as Horizontal and then go back on top and Uncheck Data Label Displaying Mode, and click OK.

Step 3: From Legend of measures, Right click on Measure (used for Column Chart) -> select Format Data Series -> select Custom -> in Data Values, Uncheck Show Data Labels, and click OK.

Step 4: From Legend of measures, Right click on Measure (used for Line Chart) -> select Format Data Series -> select Custom and move Color on from 100 to 0 -> in Data Values, Check Show Data Labels, Data Position as Outside and click OK.

Hopefully, that should fix it.

Thanks,

M Mohammed