Forum Discussion
Solved
Sum the Max Value before 0
Hello PBI Community, Hope you all are doing good. I want to sort my data based of DateTime column and the perform the sum of max value before zero. Actually I want to find Total production and c...
Hi,
Please check the attached pbix file.
Regarding 9/4/2022 shiftB, I am not sure but I added the last production number (2) and the number shows 18.
desired outcome measure V2: = VAR newtable = ADDCOLUMNS ( ADDCOLUMNS ( Data_Second, "@previousvalue", VAR _currentdate = Data_Second[Date] VAR _currenttime = Data_Second[Time] VAR _currentshift = Data_Second[Shift] VAR _previoustime = MAXX ( FILTER ( Data_Second, Data_Second[Shift] = _currentshift && Data_Second[Date] = _currentdate && Data_Second[Time] < _currenttime ), Data_Second[Time] ) VAR _previousvalue = MAXX ( FILTER ( Data_Second, Data_Second[Shift] = _currentshift && Data_Second[Date] = _currentdate && Data_Second[Time] = _previoustime ), Data_Second[Production] ) RETURN _previousvalue ), "@index", IF ( [@previousvalue] == BLANK () || Data_Second[Production] < [@previousvalue], 1, 0 ) ) VAR indexcumulatetable = ADDCOLUMNS ( newtable, "@indexcumulate", SUMX ( FILTER ( newtable, Data_Second[Time] <= EARLIER ( Data_Second[Time] ) ), [@index] ) ) VAR groupbyindexcumulatemax = SUMMARIZE ( GROUPBY ( indexcumulatetable, Shift[Shift], [@indexcumulate], "@maxtime", MAXX ( CURRENTGROUP (), Data_Second[Time] ) ), [@maxtime] ) RETURN IF ( HASONEVALUE ( Shift[Shift] ), SUMX ( FILTER ( Data_Second, Data_Second[Time] IN groupbyindexcumulatemax ), Data_Second[Production] ) )
Hi,
Please check the attached pbix file.
Regarding 9/4/2022 shiftB, I am not sure but I added the last production number (2) and the number shows 18.
desired outcome measure V2: =
VAR newtable =
ADDCOLUMNS (
ADDCOLUMNS (
Data_Second,
"@previousvalue",
VAR _currentdate = Data_Second[Date]
VAR _currenttime = Data_Second[Time]
VAR _currentshift = Data_Second[Shift]
VAR _previoustime =
MAXX (
FILTER (
Data_Second,
Data_Second[Shift] = _currentshift
&& Data_Second[Date] = _currentdate
&& Data_Second[Time] < _currenttime
),
Data_Second[Time]
)
VAR _previousvalue =
MAXX (
FILTER (
Data_Second,
Data_Second[Shift] = _currentshift
&& Data_Second[Date] = _currentdate
&& Data_Second[Time] = _previoustime
),
Data_Second[Production]
)
RETURN
_previousvalue
),
"@index",
IF (
[@previousvalue]
== BLANK ()
|| Data_Second[Production] < [@previousvalue],
1,
0
)
)
VAR indexcumulatetable =
ADDCOLUMNS (
newtable,
"@indexcumulate",
SUMX (
FILTER ( newtable, Data_Second[Time] <= EARLIER ( Data_Second[Time] ) ),
[@index]
)
)
VAR groupbyindexcumulatemax =
SUMMARIZE (
GROUPBY (
indexcumulatetable,
Shift[Shift],
[@indexcumulate],
"@maxtime", MAXX ( CURRENTGROUP (), Data_Second[Time] )
),
[@maxtime]
)
RETURN
IF (
HASONEVALUE ( Shift[Shift] ),
SUMX (
FILTER ( Data_Second, Data_Second[Time] IN groupbyindexcumulatemax ),
Data_Second[Production]
)
)
Jihwan_Kim Thanks alot, it's working perfectly, I'm accepting it as a solution.
I have posted one more issue regarding same dashboard, I'm pasting the link below, can you please check it out and look into it that how can we do it?
Looking forward for some positive responce.
Regards,
Ibad