Aggregating daily/weekly capacity by line

[Anonymous]

Hi,

I have created a measure to calculate the average capacity per production line (entered daily) but I don't understand why when I select multiple weeks, the aggregated capacity is wrong.

My Data set is as follow:

- Production is entered daily in one excel file which has 5 tabs (one per business day)

- Daily Capacity by line is entered in a master tab

Here is a example of the problem:

Week 1:

Production line 1 Capacity = 5000

Production on this line is performed on Monday,Tuesday and Wednesday (=3 days)

Week 2:

Production line 1 Capacity = 6000

Production on this line is performed on Monday,Tuesday only (=2 days)

Below is the formula (measure):

Sum Weekly Capacity =
AVERAGEX(
KEEPFILTERS(VALUES(Database[Last6weeks])),
CALCULATE(SUM('MasterRef - Line'[Line Daily Capacity]),FILTER(Database,[DAILY PLAN IN PACKS]>0)
))*Calculate(DISTINCTCOUNT(Database[DAY]),FILTER(Database,[DAILY PLAN IN PACKS]>0))

I have a slicer to select only current week + last 6 week. 

Now I create a "gauge" graph and select only week 1 => result = 15,000 (3x5000) which is perfect

But if I select week 1 and week 2 => 33,000 which is 5,000*3+6,000*3 but result should only be 5,000*3+6,000*2 = 27,000

Could you please help me understanding what I need to change in my formula to get the correct result?

Thanks,

[Anonymous]

Has anyone an idea about this problem please?

Thanks

[Anonymous · Accepted solution]

Found the solution myself.