Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Aggregating daily/weekly capacity by line

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,

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Found the solution myself.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Has anyone an idea about this problem please?

 

Thanks

Anonymous
Not applicable

Found the solution myself.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.