Forum Discussion

lgelhaar's avatar
lgelhaar
Frequent Visitor
3 years ago
Solved

Cummulative Running Total not working

I am trying to calculate a cummulative running total in Power BI Desktop, using a table and DAX calulation for a manufacturing company. The table starts with shift, hour, SKU, target, and then TargetRT ( which should be the running total). 

DAX follow:

TargetRT = VAR MaxShift = MAX('data tbl'[Shift])
                   VAR MaxHour = MAX('data tbl'[Hour])
                   VAR MaxShiftHour = MAX('data tbl'[ShiftHourSort])                                                                                                             VAR Result = CALCULATE(SUM('data tbl'[Target]), 'data tbl'[Hour] <= MaxHour, ALL('data tbl'[SKU], 
'data tbl'[HourlyStatus])))  
 
RETURN Result
 
I would like to just calculate the running target total for the whole shift, ignoring the SKU.  However, the Running total keeps breaking on the SKU.  I have tried numerous options with shift, hour and SKU in the DAX.  However, still not calculating correctly.  I use to be able to do this in MS Access.  This should be fairly easy.  There has got to be a way to get this to work.
 
See rough table below:
 

 

Need Help
  • lgelhaar's avatar
    lgelhaar
    3 years ago

    Hi, Thank you for responding.  I think that I found a solution:

     

    TargetRT = VAR MaxShift = MAX('data tbl'[Shift]) VAR MaxHour = MAX('data tbl'[Hour]) VAR MaxShiftHour = MAX('data tbl'[ShiftHourSort])                                                                                                                          
    VAR Result = CALCULATE(SUM('data tbl'[Target]), 'data tbl'[Hour] <= MaxHour, ALL('data tbl'[SKU], 'data tbl'[HourlyStatus], 'data tbl'[DowntimeMinutes], 'data tbl'[Downtime], 'data tbl'[Comments]))   RETURN Result
     

     

2 Replies

  • grantsamborn's avatar
    grantsamborn
    Solution Sage
    3 years ago

    If so, it was accomplished by making a couple of changes to you measure(s).

    TargetRT = 
// VAR MaxShift = MAX( 'data tbl'[Shift] )		// NOT referenced
VAR MaxHour = MAX( 'data tbl'[Hour] )
// VAR MaxShiftHour = MAX( 'data tbl'[ShiftHourSort] )	// NOT referenced.  
// also what is [ShiftHourSort]?
VAR Result =
    CALCULATE(
        SUM( 'data tbl'[Target] ),
        'data tbl'[Hour] <= MaxHour,
        ALL(
//            'data tbl'[SKU],				// not sure why this is included
            'data tbl'[HourlyStatus]
        )
    )
RETURN
    Result

TargetRT 2 = 
VAR MaxHour = MAX( 'data tbl'[Hour] )
VAR Result =
    CALCULATE(
        SUM( 'data tbl'[Target] ),
        'data tbl'[Hour] <= MaxHour,
        ALL(
            'data tbl'[HourlyStatus]
        )
    )
RETURN
    Result

    pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPIbeTLZTbtHAYrgSw?e=t4yIxT

     

    Please let me knowif this works.  Otherwise, please provide a more descriptive requirement.

     

    • lgelhaar's avatar
      lgelhaar
      Frequent Visitor
      3 years ago

      Hi, Thank you for responding.  I think that I found a solution:

       

      TargetRT = VAR MaxShift = MAX('data tbl'[Shift]) VAR MaxHour = MAX('data tbl'[Hour]) VAR MaxShiftHour = MAX('data tbl'[ShiftHourSort])                                                                                                                          
      VAR Result = CALCULATE(SUM('data tbl'[Target]), 'data tbl'[Hour] <= MaxHour, ALL('data tbl'[SKU], 'data tbl'[HourlyStatus], 'data tbl'[DowntimeMinutes], 'data tbl'[Downtime], 'data tbl'[Comments]))   RETURN Result