[DAX] Column and line chart: make one category as line

[Anonymous]

Hello, 

My original data is like this:

Month   Employee Type                 Hour

Jan        Matthew     Available           5

Jan       Matthew      Committed        6

Jan       Matthew      Soft Booked      2

Jan      Matthew       Total  Hour        9

Jan        Jack           Available           4

Jan       Jack            Committed        2

Jan       Jack            Soft Booked      2

Jan      Jack             Total  Hour       11

My goal is to, in the bar chart, drop the "Total Hour" categories, and make "Total Hour" (sum of that employee) as a line.

Because I have to drop "Total Hour" in the chart, I have to use a chart level filter to take them out in "Type"

Therefore, I have to use DAX to create a column for "Total Hours" that ignores the chart filter that filtered out "Total Hours". This is some "Context" thing that I'm not familiar..

Currently trything these but it's not working:

Total Capacity line = CALCULATE(SUM(Table[FTE]),Table[ASSIGNMENT_TYPE] = "Total Capacity")

 Total Capacity line = CALCULATE(SUMX(ALL(Table),Table[FTE]),Table[ASSIGNMENT_TYPE] = "Total Capacity")

Any suggestions will be helpful!!! 

Thank you!!

[Sean]

Try creating Measures for each Type - as the first one you have for Total Capacity Line

Then create a Line and Stacked Column chart!

Look at my response here - http://community.powerbi.com/t5/Desktop/Force-second-y-axis/m-p/25210#U25210

Then don't forget to Get rid of that Visual Level Filter

[Sean]

@Anonymous you need to simplify your data - let the Total be calculated...

You don't need to have TOTAL under TYPE in your original data?

Get rid of that TOTAL and then you just need a simple SUM(Table[Hour]) Measure

[Anonymous]

Hello Sean,

Thanks a lot for your reponse! However, I can't use this method becasue Business also wants me to drop "Available Capcity" in the chart, so after I filtered out Available, the SUM meansure will take out Available part as well. 

I want to make the question easy to read so I didn't add that level of detail. 

Any other suggestions? Anything will be helpful!

Thanks again!!! 

[Sean]

@Anonymous so I see you have 5 TYPEs

out of those 5 they want Available gone and Total as a line?

so 3 TYPEs in the bars and 1 line - Available not on the chart?

[Anonymous]

Yes, you are right! That's a great summary for my question! 

[Sean · Accepted solution]

@Anonymous

Create 5 Measures for each TYPE ... use your first formula = CALCULATE(SUM(Table[FTE], ...

[Anonymous]

Oh my god! You are right!! So no filter will be involved and this solved the problem! Thank you so much for your help!!