Average volume by week based on rolling calendar

[nganbla]

hi all

i have 2 table for this simply situation

1 rolling calerdar with: date (mm/dd/yyyy) / year / month / week

sales database with: product line (A / B / C ) / sold date / calculated column month & week / volume of product

I made one to many relationship btw date and sold date

I created matrix to show product sold by week / month by drill up/down => auto show total amount => it's ok now

I have a issue when try to make Average product by week.

for example: we have 6 week data (by slicer rolling calendar)

	wk 1	wk 2	wk 3	wk 4	wk 5	wk 6	Total	Avg
Product A......	1	3	4	2	3	5	18	(i want 18 / 6 week) 3
Product B......	2		4				6	i want 6/6 => 1

i create measue to count total week is 6. but when select product B, total week is count 2 only, then avg go wrong

pls help a look and guide.

thank you

[v-zhenbw-msft · Accepted solution]

Hi @nganbla ,

We can use the following steps to meet your requirement.

1. Create a week column in sales table.

week = WEEKNUM(sales[sold date],2)

2. Then create a measure to calculate the average of volume.

Measure = 
DIVIDE(CALCULATE(SUM(sales[volume of product])),CALCULATE(DISTINCTCOUNT(sales[week]),ALLSELECTED()),0)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@nganbla , Try like

AverageX(summarize(table, table[Product ], Date[Week], "_mes",Sum(Table[volume])),[_mes])

Sum till Product week level, Avg post that