Sum of value for one dimension value referring to other

[rob_vander]

Hi All,

I have below dataset. I want to create a chart with Dimension Type and Sum(Value). But, sum(value) for Type should refer to AlternateType and pick up the value for that AlternateType. for eg. for Type='A', look for 'A' in AlternateType then pick up Value. So in this case Type ='A' should give (25+10=35). 25 & 10 are the Values matched to AlternateType. likewise for all Type I need to pick up values matching with AlternateType

Load * Inline [
ID, Type, AlternateType, Value
1,a, b, 10
2,b, c, 30
3,c, c, 20
4,c, d, 15
5,d, a, 25
6,e, a, 10];

expected output:

Type	Value
a	30
b	10
c	50
d	15
e

Note: I want to achieve this using expression in chart as I don't have access to script.

[Chanty4u]

Try this 

Calculated dimension: =If(Type = AlternateType, Type, AlternateType)

Measure 

Sum(Value)

Or 

Use this expression

Sum({<Type = {"=AlternateType = Type"}>} Value)

[rob_vander]

@Chanty4u  It works. But it breaks the association. If I select Type 'a' from chart, it actually selects 'a' from AlternateType which I don't want. It has to select 'a' from Type. Also, if I select Type 'a' from filter it doesn't filter 'a' in chart but gives corresponding value of AlternateType. In short, if I select Type = a from chart or filter , selection should be made on Type field not on AlternateType

[rob_vander]

@MayilVahanan @Kushal_Chawda  @Gabbar  can you help me?

[Kushal_Chawda]

@rob_vander  I think script solution will be better in this case. Although it is achievable in expression but it will be complex and depending on size of the data it may cause performance issues.

[rob_vander]

@Kushal_Chawda  I can understand your concern but unfortunately I am restricted to change the script. However, regarding your point about data I can say that data is not huge and both type and alternate type won't have more than10 values in it.

[Kushal_Chawda · Accepted solution]

@rob_vander  try below with Type as dimension

=TextBetween(concat(total {<Type=>}aggr(Only({<Type=>}AlternateType) & Sum({<Type=>}Value) &',',AlternateType)),Type,',')

If you want total in table, enable measure total with Sum option

[rob_vander]

@Kushal_Chawda  thanks a lot.