Is it possible to do a graph like this in WEBI?

[Former Member]

Hi,

I have a requirement in which I need a graph which looks like this:

The above graph is a combination of column Chart & Stacked Column Chart.

Please let me know if this is possible.

I think that we can do this by overlapping one graph on another or merging 2 graphs. Any suggestions or Ideas?

Thanks & Regards.

[mhmohammed · Accepted solution]

Hi Naveen,

Update as of 9/21/2016 3:52 pm (EST): Instead of creating on measure IJ (for Instituted and Joinders), you can actually create 2 different measures, one for each I and J. And in Assign Data, add 2 measures I and J in the the Value Axis 1.

I was able to recreate most of it (except the formatting), with sample data having 3 columns (Fiscal Year, Decisions, Count) and following the below steps:

• Create the below 3 Measures
  1. D =If([Decisions]="Denials") Then [Count]                                   (D means Denials)
  2. IJ =If([Decisions] InList ("Instituted";"Joinders")) Then [Count]      (IJ means Instituted and Joinders)
  3. Max value on Y axis (this is used to make max value on both Y axes consistent (as we'll be using a Column Chart with 2 Y-Axes))

                    =Max(Max(If([IJ]>[D]) Then [IJ]  ElseIf([D]>[IJ]) Then [D]  Else [IJ]) In ([Fiscal Year])) In Block

• Insert a Column Chart with 2 Y-Axes and Assign Data as below and click OK
  • Category Axis = Fiscal Year
  • Value Axis 1 = IJ
  • Value Axis 2 = D
  • Region Color = Decisions
• Right click on Chart -> Format Chart -> Global, go to the each of its Sub Tab listed below and make changes as specified:
  • Global -> Data Values
    • Check Data Label Displaying Mode
    • Update Font Color to Black and/or other properties you want
    • Orientation = Vertical
  • Global -> Palette and Style
    • Click 3D Look
    • Click Single Color Palette
    • Bar Effect -> Bar Display Effect = Cylinder
  • Title -> Design -> Enter the Title Label, you can use a formula if you want the title to be dynamic
  • Legend -> Uncheck Legend Visible (to hide it), you'll have to create a table manually for the Legend
  • Category Axis -> Title -> Uncheck Title Visible (to hide it)
  • Value Axis -> Design
    • General -> Uncheck box for Visible (we want to hide it) (do this step in the end, after you verify that max value/scale on both Y axes is consistent)
    • Stacking -> click Globally Stacked Chart
    • Scaling -> in Maximum Value, click on Fixed Value, and in Value below write formula as =[Max value on Y axis] and check Round/Maximum/Minimum Values
  • Value Axis 2 -> Design
    • General -> Uncheck box for Visible (we want to hide it) (do this step in the end, after you verify that max value/scale on both Y axes is consistent)
    • Stacking -> click Globally Stacked Chart
    • Scaling -> in Maximum Value, click on Fixed Value, and in Value below write formula as =[Max value on Y axis] and check Round/Maximum/Minimum Values
• Right-click on one of the Column bars in the Instituted area -> select Format Data Series -> and update the color to Orange and click OK
• Right-click on one of the Column bars in the Joinders area -> select Format Data Series -> and update the color to Green and click OK
• Right-click on one of the Column bars in the Denials area -> select Format Data Series -> and update the color to Navy Blue and click OK
• Create a Table for the Legend Manually

Voila!

Does it resemble 75% of what you wanted? Let us know if you've any issues.

Thanks,
Mahboob Mohammed

[Former Member · Accepted solution]

Hi Mahaboob,

Thanks a lot for your detailed reply. I really appreciate your time. I think we can achieve the graph using your solution.

I have one more question,

This is how my data looks like right now:

Currently, I have 3 different measures (Instituted, Joinders and Denials) and I want them to be in a single dimension so that I can use it in the Graph (for Region Color). How do I create a new single Dimension and Measure Variable, so that I can use it in the graph?

FYI, These 3 measures (Instituted, Joinders and Denials) are coming from 3 different queries.

Please Correct me if I am wrong, to achieve this graph I think we need 2 dimensions (i.e., Fiscal Year and Decisions) and 2 measures (IJ and D)

[mhmohammed · Accepted solution]

Hi Naveen,

My assumption about the data you're working with was wrong, I assumes that there would be a column called Decision/Status, which wasn't correct.

As you've data coming from 3 different queries, just merge dimension on FY column. You'll have to create 5 Measures.

• I =If(Isnull[Instituted]) Then 0 Else [Instituted]
• J =If(Isnull[Joinders]) Then 0 Else [Joinders]
• D =If(Isnull[Denials]) Then 0 Else [Denials]
• IJ = [I] + [J]
• v_Max value on Y axis =Max(Max(If([IJ]>[D]) Then [IJ]  ElseIf([D]>[IJ]) Then [D]  Else [IJ]) In ([FY])) In Block

And in the Assign Data, specify as below

• Category Axis = FY
• Value Axis = I and click on plus sign on right to add another row and select J
• Value Axis 2 = D
• Region = leave this blank as its optional

And follow the steps as I mentioned in my previous post.

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

Hi Mahaboob,

Thanks a lot for your help . I was almost able to achieve the desired output.

I have small questions regarding the Data Values being displayed in the Graph. Sometimes the value gets displayed in the report, sometimes they don't. I am able to see values only when the font size is 2. Font Size of 2 is very small, no once can even read it. Is there any other alternative?

Picture below shows few values when Font Size is 8.

On top of it in my report, I have to display 3 graphs in a single page like this.

If the numbers don't get displayed properly the entire graph will become pointless. Please help me here.

[mhmohammed · Accepted solution]

Hi Naveen,

• If you don't want to see those light green/blue colors when there is no data, as you've shown in FY 2012 in first chart (highlighted first red box on left), then you don't have to create 3 measures I, J and D, you can directly use the measures from the query in the Chart as below.

• After you change the measures in the chart, you'll have to go to Format Chart on both Value Axes and in Stacking re-select Globally Stacked Chart.
• You still need to have IJ and Max value on Y axis. Rename IJ as Institutes and Joinders and update its formula as below  =[Instituted]+[Joinders].
• Update the formula for Max value on Y axis as =Max(Max(If([Instituted and Joinders]>[Denials]) Then [Instituted and Joinders]  ElseIf([Denials]>[Instituted and Joinders]) Then [Denials]  Else [Instituted and Joinders]) In ([FY])) In Block
• For the Font issues, go to -> Format Chart -> Global -> Data Values -> select Data Position and Inside First, Outside Otherwise and also uncheck the option Automatic Hiding Value.

Let us know if you've issues.

Thanks,
Mahboob Mohammed

[Former Member · Accepted solution]

Thanks a lot for your time Mahaboob. All my issues got resolved now

By the way, your blog posts are also really good and very informative & helpful. Please keep up the great work

[mhmohammed · Accepted solution]

Thanks Naveen, glad to help and to know you liked my blog posts.

Mahboob Mohammed

[former_member198519]

This can be helpful: