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 Target...
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
     

     

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