Issue In running Total

[AnanthanR]

Hello Everyone,

I am using Power bi Table visual. I tried to create running count on my report but, I am not getting correct value. In my table I have Container, Operation and Timestamp and Duration columns. I needs to create the running total for duration by Container and Operation. but some time the running total getting reset for next operation for same container.

for next operation its resetting the running count. below is my DAX query. I have rework flag slicer so I have added Reworflag in Dax.

Running Total Duration =

VAR CurrentDate = MAX('Report'[Time Stamp])

VAR CurrentContainer = MAX('Report'[Container])

VAR CurrentOperation = MAX('Report'[Operation])

VAR CurrentReworkFlag = MAX('Report'[Rework Flag])

RETURN

CALCULATE(

    SUM('Report'[Duration (Minutes)]),

    FILTER(

        ALL('Report'),

        'Report'[Time stamp] <= CurrentDate &&

        'Report'[Container] = CurrentContainer &&       

        'Report'[Rework Flag] = CurrentReworkFlag

    )

)

Thanks,

Ananth

[elitesmitpatel]

Please share a demo pbix file, and also your output what you want, Thanks

[AnanthanR]

Hi elismitpatel,

please find the attached screenshot for my report. I have to calculate the running total for Duration, for each container. I have marked in yellow its not getting correct running total.

here is Dax quey I have used for running total.

Running Total Duration =
VAR CurrentDate = MAX('Report'[Time Stamp])
VAR CurrentContainer = MAX('Report'[Container])
VAR CurrentOperation = MAX('Report'[Operation])
VAR CurrentReworkFlag = MAX('Report'[Rework Flag])

RETURN
CALCULATE(
SUM('Report'[Duration (Minutes)]),
FILTER(
ALL('Report'),
'Report'[Time stamp] <= CurrentDate &&
'Report'[Container] = CurrentContainer &&
'Report'[operation] <= CurrentOperation &&

'Report'[Rework Flag] = CurrentReworkFlag

))

Thanks,

Ananth

[AnanthanR]

Hi Elitesmitpatel,

Here I am not able to attach the file. Attachment option not available.

Thanks,

Ananth

[rajendraongole1]

Hi @AnanthanR - Can you try the below measure , add one more condition with operation 

Measure:

Running Total Duration =
VAR CurrentTimestamp = MAX('Report'[Time Stamp])
VAR CurrentContainer = MAX('Report'[Container])
VAR CurrentOperation = MAX('Report'[Operation])
VAR CurrentReworkFlag = MAX('Report'[Rework Flag])

RETURN
CALCULATE(
SUM('Report'[Duration (Minutes)]),
FILTER(
ALL('Report'),
'Report'[Time Stamp] <= CurrentTimestamp &&
'Report'[Container] = CurrentContainer &&
'Report'[Operation] <= CurrentOperation &&
'Report'[Rework Flag] = CurrentReworkFlag
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AnanthanR]

Thanks Rajendra for your time, I have tired the dax you provided but still i am not getting correct running total. but when i select the values in Rework slicer running count working fine.

Thanks,

Ananth

[rajendraongole1]

Hi @AnanthanR - can you try below one.

Running Total Duration =
VAR CurrentTimestamp = MAX('Report'[Time Stamp])
VAR CurrentContainer = MAX('Report'[Container])
VAR CurrentOperation = MAX('Report'[Operation])
VAR CurrentReworkFlag = MAX('Report'[Rework Flag])

RETURN
CALCULATE(
SUM('Report'[Duration (Minutes)]),
FILTER(
ALL('Report'[Time Stamp], 'Report'[Container], 'Report'[Operation], 'Report'[Rework Flag]),
'Report'[Time Stamp] <= CurrentTimestamp &&
'Report'[Container] = CurrentContainer &&
'Report'[Operation] <= CurrentOperation &&
(ISBLANK(CurrentReworkFlag) || 'Report'[Rework Flag] = CurrentReworkFlag)
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AnanthanR]

Hi Rajendra, 

Still its not working could you please help me on this.

Thanks,

Ananth