topic Single Average Value in Power Pivot / DAX in DAX Commands and Tips

Single Average Value in Power Pivot / DAX

matratus28 — Fri, 06 Sep 2024 11:51:09 GMT

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

Re: Single Average Value in Power Pivot / DAX

matratus28 — Fri, 06 Sep 2024 12:31:54 GMT

This is what I'm trying to achieve

Re: Single Average Value in Power Pivot / DAX

AnkitKukreja — Fri, 06 Sep 2024 12:37:07 GMT

Hi! matratus28

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

Re: Single Average Value in Power Pivot / DAX

matratus28 — Fri, 06 Sep 2024 14:26:47 GMT

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