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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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