Single Average Value in Power Pivot / DAX

[matratus28]

Hi,

I've got a data set of product sales and volumes.

The data is at week, product and customer level.

The 1st 13 weeks are my BASE VOLUMES and I want to calculate the average volumes for the combined 13 weeks and then track future weekly VOLUMES against the base average.

I also have a column in my data called "Timeframe" which identifies anything in the 1st 13 weeks as "Base Data" and anything after as "Live"

I'm trying to write a DAX formula that gives me the average weekly volumes so I have a single value as the average for the 1st 13 weeks.

When I pull my measure into my pivot it gives me the weekly average figure (so a different figure for each week) rather than the combined 13 week average. (the average figure needs to be consistent so it can be used in a line chart as the average)

Can anyone help please?

I've been trying various versions of this:

AverageBaseQty:=CALCULATE(

SUM(t_volumes[EACHES]) ,

't_volumes'[Timeframe] = "Baseline"

) /13

[matratus28 · Accepted solution]

I found a solution that looks for all the volumes if in the week is before 202514 and divides by 13
That then overrides the week in the pivot table and gives just the average for the entire 13 weeks

BaseAverageEACHES:=CALCULATE(

SUM(t_volumes[EACHES]),

t_volumes[Timeframe] = "Baseline",

t_volumes[Week] <=202513

) /13

[AnkitKukreja]

Hi! @matratus28 

Is it possible to share the data after masking/removing pii information to have a look at it.

[matratus28]

This is what I'm trying to achieve