Add the aggregate line to a line chart with multiple lines

[Anonymous]

Dear all.

I have a following data :

Category  Sub category  Month  Price  Sales

A               A1                    1       100         10

A               A1                    2       100         10

A               A1                    3       100         10

A               A1                    4       100         10

A               A1                    5       100         10

A               A1                    6       100         10

A               A2                    1       100         10

A               A2                    2       100         10

A               A2                    3       100         10

A               A2                    4       100         10

B               B1                    1       100         10

B               B1                    2       100         10

B               B1                    3       100         10

B               B1                    4       100         10

B               B1                    5       100         10

B               B1                    6       100         10

B               B2                    1       100         10

B               B2                    2       100         10

B               B2                    3       100         10

B               B2                    4       100         10

C               ......................................................

I don't want to hard code the expressions, since I'd like the user to be able to select the category  names and then display the results accordingly. The line chart is monthly basis.If user chose A Category, the chart will show three monthly trend lines, one is for A1 monthly price trends, one is for A2 monthly trends and the third one is aggregation line. The logic for aggregation is sumproduct(price,sales)/total sales.

Appreciate if any ideas.

Thanks!!!

[Gysbert_Wassenaar]

What's your question?

[sunny_talwar]

Would you be able to share how the expected output looks like (in terms of number) to help you out better here?

[johnw]

I don't know if I've understood, but if I have, maybe remove Category from that table, and add a new table:

Category, Sub Category with Category Total, Sub Category
A, A total, A1
A, A total, A2
A, A1, A1
A, A2, A2

...

And do your chart like this:

Dimension = Month
Dimension = Sub Category with Category Total
Expression = sum(Price * Sales) / sum(Sales)

[Anonymous]

For example, if filtering A category, the charts should display three lines.

One for subcategory A1 monthly price trend, one for subcategory A2 monthly price trend

the third line should show aggregation . The logic for aggregation is sumproduct(price,sales)/total sales for subcategory A1 and A2.

Is it clear?

[Gysbert_Wassenaar]

Use the solution johnw provided above.

[#7]

Just like John Witherspoon said,

see my sample,if could any help.