Scenario 2 (Gap Analysis using floating Bar chart)

Scenario 2: (Complex)
Problem: Gap analysis between demand vs supply with floating gap bar along with conditional formatting. See below image.
Gap = Supply - Demand

  1. The gap that is Supply – Demand should display in between supply and demand bars.
  2. If the gap is -ve mean supply is less than demand then gap bar should be in red color or if the gap is +ve means supply is more than demand then gap bar should be in green color.
  3. In the same way gap bar should start from end of demand bar if it is less than supply or it should start from end of supply bar if is less than demand.

Questions that I raised:
  1. How to display Gap bar between Demand and Supply in floating mode.
  2. How to make Gap bar start from end of either Demand or Supply (start point depends upon which one is low among Demand and Supply)
  3. How to do conditional formatting for floating bar.
please follow step by step clearly
This report build with sample data that I prepared please see the sample data

Step 1: In present dataset we don’t have gap so we need to calculate gap using below formula. Create a calculated field “Supply - Demand (Gap)”

[Supply] - [Demand]

Step 2: Create a calculated field for conditional formatting as “+ve or -ve Gap” (solution of my 3rd Question)

if [Supply - Demand (Gap)] < 0 then "-ve Gap" else "+ve Gap" END

Step 3: Drag year column to filter and select 2014. Drag Quarter column to column shelf , Drag Demand , Supply columns to row shelf. Go to Show me and select side by side bar. You will see like this

Step 4: Create a calculated field as “Gap (ABS)” to get the Absolute value of Gap.

float(abs([Supply - Demand (Gap)]))

Step 5: Create a calculated field “Gap” to make the starting point of floating bar. This will depend upon Demand and Supply values. Which is low among these two will become start point for floating bar.

if sum([Supply]) > sum([Demand]) then float(sum([Demand])) else float(sum([Supply])) END

Step 6: Drag “Gap” column that created in step 5 to rows shelf, beside “Measure Values”. See below

Step 7: Right click on “Gap” axis and select dual axis. If is not showing bar chart then go to marks shelf and select bar. It should like this

Step 8: Right click on “Gap” axis which on right side and select “Synchronize Axis”, again right click and click on Show Header. Then the right side Axis will go hide.

Step 9: Go to Marks shelf, select “Gantt Bar” in Gap marks shelf drop down.

Step 10: Drag “Gap (ABS)” field which is created in step 4 to size shelf. Now you will see floating gap bars in chart.

Step 11: Drag conditional formatting calculated field “+ve or -ve Gap” which is created in step 2 to Gap marks shelf.

After adding conditional formatting field to Gap marks shelf report will looks like this:

Step 12: Now we have to show labels to bars. Just click on “Abc” from tool bar and format labels to standard format.

Step 13: See the label for gap floating bar it showing either Demand or Supply values because we use them for starting point of float bar. So we need to change this label. Go to label shelf of Gap marks shelf, unchecked show mark labels.

Step 14: Now Drag “Supply - Demand (Gap)” field which created in step 1 to label shelf of Gap Marks Shelf. Finally report is completed.

See the report below: