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,
