cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
AA622
Helper II
Helper II

Total is not adding up correctly

Hello

 

I am trying to calculate total batch time. Please refer to the image below.

AA622_0-1671217396759.png

 

The column named Cycle time shows the amount of days between start date + end date. Even if its 1 day difference the cycle time is 2. I am having trouble getting the total to show the actual cycle time amount. Based on the values for this particular item it should be 22 but i am getting 211. I have no idea where that is coming from. There are only 19 rows for this 1 item. 15 of those rows show a value and 4 do not. Either way it comes out to 22 but it is showing 211.

 

This is the measure I am using to count cycle time. I think the discrepancy is coming from the empty rows, just a hunch.

 

Prod_CycleTime_Workday = SUMx(
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN( 'Cycle Time'[Production_Start date] )
            &&
        'Calendar'[Date] <= MAX( 'Cycle Time'[Production_End date] )
    ),
    'Calendar'[IsWorkdayNoHoliday]
)
 
 
Is something wrong with the measure that is getting me 211?
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @AA622 ,
a possible solution could look like so:

Measure = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[ItemID], 'Table'[ProductionID] ),
    VAR _StartTime =
        CALCULATE ( MIN ( 'Table'[StartTime] ) )
    VAR _EndTime =
        CALCULATE ( MAX ( 'Table'[EndTime] ) )
    RETURN
        CALCULATE (
            SUM ( 'Calendar'[IsWorkdayNoHoliday] ),
            'Calendar'[Date] >= _StartTime,
            'Calendar'[Date] <= _EndTime
        )
)

Next time, please provide sample data in a usable form.
Please also see file attached.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User
Super User

Hi @AA622 ,
a possible solution could look like so:

Measure = 
SUMX (
    SUMMARIZE ( 'Table', 'Table'[ItemID], 'Table'[ProductionID] ),
    VAR _StartTime =
        CALCULATE ( MIN ( 'Table'[StartTime] ) )
    VAR _EndTime =
        CALCULATE ( MAX ( 'Table'[EndTime] ) )
    RETURN
        CALCULATE (
            SUM ( 'Calendar'[IsWorkdayNoHoliday] ),
            'Calendar'[Date] >= _StartTime,
            'Calendar'[Date] <= _EndTime
        )
)

Next time, please provide sample data in a usable form.
Please also see file attached.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

Hi @AA622 ,
you are probably iterating the wrong table here: At the total-level, there will be only one value for the MIN'Cycle Time'[Production_Start date] ) and MAX( 'Cycle Time'[Production_Start date] ). Those will be the earliest and latest dates for ALL start dates in your CycleTime-table. This will basically remove all filters on the iterated items and cause the number to be that high.

Instead, you should iterate a table that contains the ItemID and ProductionsID-fields. (Hard to be more specific here without seeing the details of your data model).
Then, make sure to use a CALCULATE around the 'Calendar'[IsWorkdayNoHoliday] , to cover the effects of the row context within iterators: CALCULATE('Calendar'[IsWorkdayNoHoliday]) 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hello

 

What more details do you need? All the fields for this particular report are housed within the same table from a SQL query join. Item ID and productionID are in the same table. You can call it Cycle Time if that helps.

 

I understand that it needs to iterate from the ItemId/Production ID but I am having trouble structuring the new measure. Any insight?

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors