How to create averge line into multiple line chart

[Anonymous]

Hi all,

I attached my raw data and desired output in Excel File.The line chart is monthly basis. The chart will show  monthly trend lines for each model, and also want to add  the monthly simple average line for all models.

I also uploaded my QV file, it seemed wrong in expression. It is right for average but wrong for model trends.

Can anyone help me please.

[Anonymous]

Yes. The output is what I want to . But the average value seems not to be right, and one more question" Can I achieve this if I don't want to load any extra data?

[sunny_talwar]

What's wrong with the average? Apr 2016 number for average is matching and so in Feb 2016 (did not check all the averages, but they should all match). The only thing different is the fact that your MonthYear is not sorted so the chart looks slightly different

There might be a very complicated way to do this without an Island Table, but I would suggest against it. This is a very simple one field Island Table which won't even impact a craziest of data models.

[Anonymous]

In you example, you choose Model=A&b&c, so the average means average of ABC, not all models

[sunny_talwar · Accepted solution]

So you want to see only the average for the three models selected? Try this:

Pick(Dim, Sum([Transaction Price]), Avg(TOTAL <Month> Aggr(Sum([Transaction Price]), Month, Model)))

[Anonymous]

Thanks Sunny. This is what I want. But one more question, Can I do this base on my data? I mean if I don't want to load extra dim data.

[sunny_talwar]

I did respond to that part of your question above:

"There might be a very complicated way to do this without an Island Table, but I would suggest against it. This is a very simple one field Island Table which won't even impact a craziest of data models."

[sunny_talwar]

If you really want to do this without Island Table, may be mrkachhiaimp‌ can share a sample which we worked on when we were playing around with this idea of not creating an Island table

Off to sleep now....

Best,

Sunny

[Anonymous]

Cool. Ok. I understand.

Can I set this question more complicated?

In the above example, average is simple average. If I want to use another weighted average line to replace simple average, can it be realized?

The logic for weighted average is (sumproduct of price and sales) /sum of sales.

Sorry for occupying so much time.

Really appreciate!!!

[sunny_talwar]

‌Yes sir... make it complicated, but I won't be able to get to this until tomorrow morning.

Best,

Sunny

[Anonymous]

Sure. Have a nice sleep.

I will wait for you for the answer!

Thanks again.

Jerry