Trend line based on user selection

[Anonymous]

I want to create a trend line chart where users can compare their country specific sales trend against their region and division average. I.e three trend lines.

Three level of hierarchies in my data Division-Region-Country. The division and country should be set based on user selection. I feel like set analysis is the way but don't seem to get it working. can someone guide me on this?

Sample data attached

Thanks in advance.

[Sergey_Shuklin · Accepted solution]

Hello!

In your sample data the Division field is equal to Region field, in such case you'll see no difference between them on chart.

Find the attached file, there are expressions with the set analysis you need.

[Anonymous]

Perfect, thanks a lot Sergey!

The trend appears to jumps up and down quite a bit. Any suggestion how to do the rolling 12 month trend?

[Sergey_Shuklin]

Hello!

For rolling-calculations you can use RangeSum(Above(... construction. See attached file.

I also let myself to add some cosmetical things. You can find them useful too.

[Anonymous]

Very helpful, thank you so  much.