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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lgelhaar
Frequent Visitor

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:
 
lgelhaar_0-1673531916701.png

 

1 ACCEPTED SOLUTION

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
 
lgelhaar_0-1673542761226.png

 

View solution in original post

2 REPLIES 2
grantsamborn
Solution Sage
Solution Sage

Results.png

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.

 

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
 
lgelhaar_0-1673542761226.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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