Powerpivot chart

[KVAMSHE]

Mr.Excel,

Am really very happy to take your help/tips on excel.

I have couple of requirements as mentioned below:

1. In powerpivot stacked chart am not able to see grand totals column is being selected automatically. And even am not able to select the series because it is disabled. When i tried to add series manully then then the problem is that chart is not aligned as per slicer selection. Please advise me how can i show grand totals in the chart. (see link below)
http://www.2shared.com/file/I53GmC6c/Example.html


2. Is there any function similar to sumif because i want to add text data into one single cell through vlookup or look because source data & target data files are different
Source file data-
Name Travelled Region
A USA
B APA
C EU
B USA
C APA
B EU

Target file-
Name Region
A =(formula) i.e. USA
B =(formula) i.e. APA/USA/EU
C =(formula) i.e. EU/APA

3. When used counta formula in Measure condition the grand totals is not aligned/filtered to exact breakup but it is showing original grand totals
[TABLE="width: 207"]
<TBODY>[TR]
[TD="class: xl65, width: 44, bgcolor: #dce6f1"]Name
[/TD]
[TD="class: xl65, width: 79, bgcolor: #dce6f1"]Permanent
[/TD]
[TD="class: xl65, width: 74, bgcolor: #dce6f1"]Temporary
[/TD]
[TD="class: xl65, width: 78, bgcolor: #dce6f1"]Grand Total
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A
[/TD]
[TD="bgcolor: transparent, align: right"]39
[/TD]
[TD="bgcolor: transparent, align: right"]250
[/TD]
[TD="bgcolor: transparent, align: right"]289
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B
[/TD]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[TD="bgcolor: transparent, align: right"]63
[/TD]
[TD="bgcolor: transparent, align: right"]81
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]77
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]35
[/TD]
[TD="bgcolor: transparent, align: right"]55
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E
[/TD]
[TD="bgcolor: transparent, align: right"]26
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]G
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]J
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]K
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]50
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #dce6f1"]Grand Total
[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]177
[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]479
[/TD]
[TD="class: xl66, bgcolor: #dce6f1, align: right"]656
[/TD]
[/TR]
</TBODY>[/TABLE]

Request you to advise with formulas/way goforward.

Advance thanks
Vamshe