Forum Discussion

Frequent Visitor

8 years ago

Solved

stacked chart

HI

I have Measures A & B on stacked Y axis and Measure C on Line Yaxis chart. and i have months as X axis.

i am using stack and Line chart.

i want measure B to be sliced within based upon some status.

you can see below, i am reporting sales from unit A & unit B as two measures on Y axis. i want Unit B to be sliced within by sales person.

Unit A measure should show as full without any slicing.

how can we do it.

MFelix
8 years ago
Hi sbkrishblr,

I would do the following changes to your information:

Create a calendar table

Unit A table:
Add Columns:
Salesman - Blank
Unit - "Unit A"
Unit B Table
Add Columns:
Unit - "Unit B:" & Salesman
Append Table A with table B having a single table (Sales) with:
Month
Sales
Salesman (for Unit A this values will be blank)
Unit
Connect the Calendar table with the Sales table and with the Target table

Make the chart with the following setup
X-axis - Date from Calendar
Column Series - Unit
Column values - Sales
Line Values - Company Target
Final result below:

Regards,
MFelix

6 Replies

MFelix
Super User
8 years ago
Hi sbkrishblr,

Can you share some sample data?

Regards,
MFelix

Frequent Visitor

please find the sample data.

Stacked Chart	Unit A Sales
	Unit B Sales

Line	Company Target

Month	Unit A Sales
Jan-17	200
Feb-17	300
Mar-17	200
Apr-17	200
May-17	300
Jun-17	200
Jul-17	200
Aug-17	300
Sep-17	200
Oct-17	200
Nov-17	300
Dec-17	200

Month	Unit B Sales	SalesMan
Jan-17	50	Tom
Jan-17	70	Harry
Jan-17	100	Jack
Feb-17	60	Tom
Feb-17	50	Harry
Feb-17	30	Jack
Mar-17	50	Tom
Mar-17	100	Harry
Apr-17	65	Jack
Apr-17	55	Harry
May-17	160	Tom
May-17	50	Harry
May-17	100	Jack
Jun-17	30	Tom
Jun-17	110	Jack
Jul-17	50	Tom
Jul-17	70	Harry
Jul-17	100	Jack
Aug-17	60	Tom
Aug-17	150	Harry
Aug-17	130	Jack
Sep-17	150	Tom
Sep-17	100	Harry
Oct-17	80	Jack
Oct-17	90	Harry
Nov-17	160	Tom
Nov-17	150	Harry
Nov-17	150	Jack
Dec-17	200	Tom
Dec-17	200	Jack

Month	Company Target
Jan-17	600
Feb-17	600
Mar-17	800
Apr-17	800
May-17	800
Jun-17	900
Jul-17	900
Aug-17	900
Sep-17	900
Oct-17	1000
Nov-17	1000
Dec-17	1000

MFelix
Super User
8 years ago
Hi sbkrishblr,

I would do the following changes to your information:

Create a calendar table

Unit A table:
Add Columns:
Salesman - Blank
Unit - "Unit A"
Unit B Table
Add Columns:
Unit - "Unit B:" & Salesman
Append Table A with table B having a single table (Sales) with:
Month
Sales
Salesman (for Unit A this values will be blank)
Unit
Connect the Calendar table with the Sales table and with the Target table

Make the chart with the following setup
X-axis - Date from Calendar
Column Series - Unit
Column values - Sales
Line Values - Company Target
Final result below:

Regards,
MFelix
- sbkrishblr
  Frequent Visitor
  8 years ago
  Hi,
  
  thanks for replying with your solution. we also did it exactly the same way... but when we select the filter for any of the unit B- salesman, the block gets highlighted, and rest other blocks including ( unit A block & unit B- other salesman ) gets grayed out.
  
  What we want is, when we click on one unit B- Salesman ( say Unit B - Tom), then only unit B - Jack & unit B - Harry blocks should grey out, Unit A should not be grayed out.
  
  Hope my point is clear.
  
  thanks,
  suresh K