stacked chart

[sbkrishblr]

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.

[sbkrishblr]

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 · Accepted solution]

Hi @sbkrishblr,

I would do the following changes to your information:

Create a calendar table

Unit A table:

1. Add Columns:
   1. Salesman - Blank
   2. Unit - "Unit A"

Unit B Table

1.  Add Columns:
   1. Unit - "Unit B:" & Salesman

Append Table A with table B having a single table (Sales) with:

1. Month
2. Sales
3. Salesman (for Unit A this values will be blank)
4. Unit

Connect the Calendar table with the Sales table and with the Target table 

Make the chart with the following setup

1. X-axis - Date from Calendar
2. Column Series - Unit
3. Column values - Sales
4. Line Values - Company Target

Final result below:

Regards,

MFelix

[sbkrishblr]

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

[erik_tarnvik]

Hi @sbkrishblr,

depending on your exact requirements, you may be able to achieve something close to what you are asking using the tecnique in the answer in the below post. By using a parameter table you can customize visual selection behavior using a slicer and vsiual filters.

https://community.powerbi.com/t5/Desktop/Dynamic-column-based-on-slicer/m-p/268231#M120994

[MFelix]

Hi @sbkrishblr,

That is not possible since the seceval colours in the graph are relating only to one salesman you can' do it with a songle click only with CRTL+ click on the different values you want.

Regards,
MFelix

[MFelix]

Hi @sbkrishblr,

Can you share some sample data?

Regards,

MFelix