Evolution trend over a year

[Saryk]

I have a combo graph of sales per month as bars ; and a line that shows the average since january :

=RangeAvg(Above(Sum([Sales]), 0, 12))

This is for a yearly review so I only load data for 2019 ; average for January is just January, for february it is Jan+Feb/2, then Jan+Feb+March/3, etc.

I want to have a percentage of [this average for december] / [this average for january] * 100, but I don't know how to synthax the Month selection :

=
RangeAvg(Above(Sum({<Month={'01'}>}[Sales]), 0, 12))
/
RangeAvg(Above(Sum({<Month={'12'}>}[Sales]), 0, 12))

Thanks in advance, cheers !

[sunny_talwar]

You are trying to calculate this average in which object? Also, would you be able to provide few rows of data and the output you are hoping to see from it?

[Saryk]

I'd like it in a Gauge chart, so I only have access to a measure. Data is ordered by date ; but I just realized that Above(,12) will be in data rows, not in months.

The current formula returns nothing (' - ').

Here is some inline data :

[
month, sales
1, 13376
2, 9547
3, 3599
4, 6442
5, 7281
6, 11590
7, 3991
8, 3532
9, 9145
10, 9751
11, 5459
12, 4739
]

In combo chart with [month] dimension, [sales] bars, and a line with the following expression :

=RangeAvg(Above(Sum([sales]), 0, 12))

This should look like this :

I then want in a gauge chart to have 1 - the december average / the january sales, so here 1 - 7371 / 13376 = 44,89%, and I don't know how to write that in the gauge measure formula.

[sunny_talwar · Accepted solution]

Try this

1 - (Avg(Aggr(Sum(sales), month))) / Sum({<month = {1}>} sales)

[Saryk]

It returns ' - ' ...

[sunny_talwar]

Field names are case sensitive.... did you make sure to change month to the field name that you have? Which might be Month or Order_Month or MonthName etc... similarly you might need to fix sales.

Also, month field (or whatever you call it) is formatted as number? Is it 1, 2, 3 or Jan, Feb, Mar?

[Saryk]

Aye sir that was it !

My Month is formatted as "Jan. 2019", so I put that in and it works.

Thank you very much 😄

[sunny_talwar]

Super