Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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,
Solved! Go to Solution.
Has anyone an idea about this problem please?
Thanks
Found the solution myself.
User | Count |
---|---|
134 | |
74 | |
73 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |