topic Re: Divide values by average of a period of time in DAX Commands and Tips

Divide values by average of a period of time

Anonymous — Mon, 24 May 2021 13:44:26 GMT

Hi, I currently have a bar chart.

- X axis: Year-Month

- Y axis: count

- Legend: categories

Where at each x value I have multiple bars, one for each category. I have to divide the count value by the average value of that specific category. I have achieved this with the following DAX command:

DIVIDE(
SUM(programs[count]),
CALCULATE(AVERAGE(programs[count]), ALLSELECTED(programs[date]))
)

The problem is that I need to divide all the values by the average of the last three months. I have tried the following but it does not take into account different groups, it calculates the average of the last 3 months but even though the SUM(programs[count]) filters by category automatically, the average is calculated on all categories:

velocity_variance =
VAR latest_date = 21.06
VAR margin = 3
VAR start_date = CONVERT(latest_date - margin, STRING)
VAR end_date = CONVERT(latest_date , STRING)
VAR velocity = DIVIDE(SUMX(FILTER(programs, AND(programs[data] >= start_date, programs[date] < end_date)), programs[count]), margin)
RETURN
DIVIDE(
SUM(programs[count]),
velocity
)

Based on this query, I can only think of creating a different average measure for each category but I don't want this because categories will change in the future and I have a lot.

The programs table is of the shape:

Date	Count	Category
20.01	34	A
...	...
21.12	654	C

I hope someone can help me!

(sorry about the code, the editor wouldn't let me add syntax highlighting)

Thanks.

Re: Divide values by average of a period of time

amitchandak — Mon, 24 May 2021 16:02:33 GMT

Anonymous , Not very clear to me.

For Avg of last 3 months you can try like

example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date]),0),-3,MONTH))/3

Re: Divide values by average of a period of time

Anonymous — Tue, 25 May 2021 06:38:31 GMT

amitchandak Hi, thanks for your help, sorry for the explanation I will explain it better:

1. I need to create a bar chart where each value is divided by the average

2. I have done this but I need that the average is only of the last 3 months

3. If I try your method it works but it does the average on all categories not each category individually

Re: Divide values by average of a period of time

Anonymous — Tue, 25 May 2021 07:12:31 GMT

Okey, I think the problem is solved, thanks for your help 🙂