Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 counter of production is incremented and count of previous production is added in new count of production. When the shift changes or machine stops due to any reason, counter is reset to 0. I want to find shift wise production. Below is the sample data for reference and want to sum the Red Highlighted Bold Values based on scenario.
Time | Shift | Production |
7:00:00 AM | Shift A | 0 |
7:02:00 AM | Shift A | 5 |
7:04:00 AM | Shift A | 10 |
7:06:00 AM | Shift A | 15 |
7:08:00 AM | Shift A | 0 |
7:10:00 AM | Shift A | 5 |
7:12:00 AM | Shift A | 10 |
7:14:00 AM | Shift A | 15 |
7:16:00 AM | Shift A | 20 |
7:18:00 AM | Shift A | 25 |
7:20:00 AM | Shift A | 30 |
7:22:00 AM | Shift B | 0 |
7:24:00 AM | Shift B | 5 |
7:26:00 AM | Shift B | 10 |
7:28:00 AM | Shift B | 15 |
7:30:00 AM | Shift B | 0 |
7:32:00 AM | Shift B | 5 |
7:34:00 AM | Shift B | 10 |
7:36:00 AM | Shift B | 15 |
7:38:00 AM | Shift B | 20 |
7:40:00 AM | Shift B | 0 |
Result I want is,
Shift A = 15 + 30 = 45
Shift B = 15 + 20 = 35
Looking forward for some postive responce.
Regards,
Ibad.
@amitchandak @DataInsights @v-zhangti @ribisht17
Solved! Go to Solution.
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]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Schedule a short Teams meeting to discuss your question
Hi,
Please check the below picturea and the attached pbix file.
desired outcome measure: =
VAR newtable =
ADDCOLUMNS ( Data, "@index", IF ( Data[Production] = 0, 1, 0 ) )
VAR indexcumulatetable =
ADDCOLUMNS (
newtable,
"@indexcumulate", SUMX ( FILTER ( newtable, Data[Time] <= EARLIER ( Data[Time] ) ), [@index] )
)
VAR groupbyindexcumulatemax =
SUMMARIZE (
GROUPBY (
indexcumulatetable,
Shift[Shift],
[@indexcumulate],
"@maxtime", MAXX ( CURRENTGROUP (), Data[Time] )
),
[@maxtime]
)
RETURN
SUMX (
FILTER ( data, Data[Time] IN groupbyindexcumulatemax ),
Data[Production]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Schedule a short Teams meeting to discuss your question
Hey @Jihwan_Kim thanks alot for your support and quick responce. It's working corrctly according to given condition, but I got an other scenario that if the next value is less than current value (not specifically 0) than consider current value as the Max of counter for calculating production.
Below is the sample data for your understanding,
Date | Time | Shift | Production |
8/4/2022 | 7:04:00 AM | Shift A | 10 |
8/4/2022 | 7:06:00 AM | Shift A | 15 |
8/4/2022 | 7:08:00 AM | Shift A | 0 |
8/4/2022 | 7:10:00 AM | Shift A | 5 |
8/4/2022 | 7:12:00 AM | Shift A | 10 |
8/4/2022 | 7:14:00 AM | Shift A | 15 |
8/4/2022 | 7:16:00 AM | Shift A | 20 |
8/4/2022 | 7:18:00 AM | Shift A | 25 |
8/4/2022 | 7:20:00 AM | Shift A | 30 |
8/4/2022 | 7:22:00 AM | Shift B | 5 |
8/4/2022 | 7:24:00 AM | Shift B | 10 |
8/4/2022 | 7:26:00 AM | Shift B | 15 |
8/4/2022 | 7:28:00 AM | Shift B | 20 |
8/4/2022 | 7:30:00 AM | Shift B | 4 |
8/4/2022 | 7:32:00 AM | Shift B | 5 |
8/4/2022 | 7:34:00 AM | Shift B | 10 |
8/4/2022 | 7:36:00 AM | Shift B | 15 |
8/4/2022 | 7:38:00 AM | Shift B | 20 |
8/4/2022 | 7:40:00 AM | Shift B | 0 |
9/4/2022 | 7:00:00 AM | Shift A | 2 |
9/4/2022 | 7:02:00 AM | Shift A | 4 |
9/4/2022 | 7:04:00 AM | Shift A | 6 |
9/4/2022 | 7:06:00 AM | Shift A | 2 |
9/4/2022 | 7:08:00 AM | Shift A | 4 |
9/4/2022 | 7:10:00 AM | Shift A | 1 |
9/4/2022 | 7:12:00 AM | Shift A | 4 |
9/4/2022 | 7:14:00 AM | Shift A | 6 |
9/4/2022 | 7:16:00 AM | Shift A | 8 |
9/4/2022 | 7:18:00 AM | Shift A | 10 |
9/4/2022 | 7:20:00 AM | Shift A | 12 |
9/4/2022 | 7:22:00 AM | Shift B | 0 |
9/4/2022 | 7:24:00 AM | Shift B | 2 |
9/4/2022 | 7:26:00 AM | Shift B | 4 |
9/4/2022 | 7:28:00 AM | Shift B | 6 |
9/4/2022 | 7:30:00 AM | Shift B | 0 |
9/4/2022 | 7:32:00 AM | Shift B | 4 |
9/4/2022 | 7:34:00 AM | Shift B | 6 |
9/4/2022 | 7:36:00 AM | Shift B | 8 |
9/4/2022 | 7:38:00 AM | Shift B | 10 |
9/4/2022 | 7:40:00 AM | Shift B | 2 |
Desired output is:
8/4/2022 | Shift A | 45 |
8/4/2022 | Shift B | 40 |
9/4/2022 | Shift A | 22 |
9/4/2022 | Shift B | 16 |
Looking forward for your responce.
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]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Schedule a short Teams meeting to discuss your question
@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
@Ibad_Khan , create a new column
Column = var _max = Minx(filter(Data, [Shift] = EARLIER([Shift]) && [Time] > EARLIER([Time])), [Time])
Var _val = Minx(filter(Data, [Shift] = EARLIER([Shift]) && [Time] =_max ), [Production])
return if(_val =0 , [Production], BLANK())
You can use this in measure now
User | Count |
---|---|
84 | |
81 | |
64 | |
53 | |
45 |
User | Count |
---|---|
100 | |
48 | |
41 | |
39 | |
38 |