Forum Discussion
Resolver ISingle Average Value in Power Pivot / DAX
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
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 weeksBaseAverageEACHES:=CALCULATE(
SUM(t_volumes[EACHES]),
t_volumes[Timeframe] = "Baseline",
t_volumes[Week] <=202513
) /13
3 Replies
- AnkitKukreja
Super User
Hi! matratus28
Is it possible to share the data after masking/removing pii information to have a look at it.
matratus28This is what I'm trying to achieve
- matratus28
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 weeksBaseAverageEACHES:=CALCULATE(
SUM(t_volumes[EACHES]),
t_volumes[Timeframe] = "Baseline",
t_volumes[Week] <=202513
) /13
