Summing values in column from the mark in other column

[pak]

Hello everyone,

I'm begginer with Power BI and i need your help 🙂

This is my problem:

I must sum overal WorkTime of the machine but after repair the WorkTime must be sumed from zero. So i added in the machine  button that is pressed after repair. When it's pressed the Mark bit (1) is send to ASA.
In Power BI Desktop i dont have tables, only fields and measures. I get my data from ASA.
But i added table that show you what i mean.

Date and Time	WorkTime	Mark
2017-12-12 16:00:00	120
2017-12-12 16:00:10	122
2017-12-12 16:00:20		1
2017-12-12 16:00:30	125
2017-12-12 16:00:40
2017-12-12 16:00:50	111
2017-12-12 16:01:00	123
2017-12-12 16:01:10	112
2017-12-12 16:01:20	123
2017-12-12 16:01:30	145	1
2017-12-12 16:01:40	156
2017-12-12 16:01:50	125
2017-12-12 16:02:00
2017-12-12 16:02:10	123
2017-12-12 16:02:20	121
2017-12-12 16:02:30	113
2017-12-12 16:02:40
2017-12-12 16:03:00	154

So in the raport i want to see sum of WorkTime from the moment where Mark = 1.
for example:

If there's 16:01:20 , and i'm refreshing raport i should get value 594.
If there's 16:03:00 , and i'm refreshing raport i should get value 792.

This would be very helpfull 🙂

[pak]

Anyone??

The thing is i need it tommorow 😕

[parry2k]

Hey @pak

I worked on the solution but was unable to match the 2nd data result you gave and seems like there was typo at your end.

Here is how you can achieve it:

Add Rank column

Rank = 
RANKX(Filter(Machine, Machine[Mark] <> BLANK() ), Machine[Date and Time], , asc, Dense) 
+ Machine[Mark]

Add measure called "Total Worktime"

Total Worktime = 
CALCULATE(
    SUM(Machine[WorkTime]), 
    Filter(
        ALLSELECTED(Machine), 
        Machine[Rank] = MAX(Machine[Rank]) && 
        Machine[Date and Time] <= MAX(Machine[Date and Time])
    )
)

Drop table visual on canvas and add following fields:

- Date and Time

- Work Time

- Total Work Time

[pak]

 Hey @parry2k

Thank you for your answer.

The problem is, as i wrote before... i don't have any tables and no ablility to create ones, create colums etc. All tabels functions are inactive 😕

I get my dataset this way: IotHub -> Azure -> StreamAnalitysc -> (through PowerBI outputs) -> PowerBI

In PBI Dektop i chose Get Data -> Online Service -> Power BI Service

[pak]

@parry2k... i was wondering. Maybe i could do this with summing resetBit value and summing WorkTime from 0 for different values in Sum(Mark) column. Table explain what i mean, but as i said i dont have tables, just metrics:

Date and Time	WorkTime	Mark	Sum(Mark)
2017-12-12 16:00	120
2017-12-12 16:00	122		0
2017-12-12 16:00		1	1
2017-12-12 16:00	125		1
2017-12-12 16:00			1
2017-12-12 16:00	111		1
2017-12-12 16:01	123		1
2017-12-12 16:01	112		1
2017-12-12 16:01	123		1
2017-12-12 16:01	145	1	2
2017-12-12 16:01	156		2
2017-12-12 16:01	125		2
2017-12-12 16:02			2
2017-12-12 16:02	123		2
2017-12-12 16:02	121		2
2017-12-12 16:02	113		2
2017-12-12 16:02			2
2017-12-12 16:03	154		2

So, for Sum(Mark)=1 Sum of Work time = 594.
For Sum(Mark)=2 Sum of Work time = 792.

Can i do this somehow?

[pak]

Or i can send Mark that is already summed like this:

Date and Time	WorkTime	Mark
2017-12-12 16:00	120	0
2017-12-12 16:00	122	0
2017-12-12 16:00	125	1
2017-12-12 16:00	111	1
2017-12-12 16:01	123	1
2017-12-12 16:01	112	1
2017-12-12 16:01	123	1
2017-12-12 16:01	145	2
2017-12-12 16:01	156	2
2017-12-12 16:01	125	2
2017-12-12 16:02	123	2
2017-12-12 16:02	121	2
2017-12-12 16:02	113	2
2017-12-12 16:03	154	2

Which one would be better to do what i want?

[parry2k]

2nd option looks great. Assuming Mark is grouping the data together, and it will be always new after a stop time.

[pak]

Yea it worked, thanks parry2k so this is solved, but i have question about another thing which i had in same project.

I don't have ability to change input value frome sum to just showing the last value. For examle when i take Card from visialisation and put ther WorkTime value it will show me sum of every WorkTime values - i can't change it to last one.

So i tried to use this function:

LastWorkTime = CALCULATE(SUM('Machine'[WorkTime]);LASTNONBLANK('Machine'[Date and Time];1))

but when i did that it return me "null" value.

So i am now sending Mark2 value with each WorkTime sending which is increased by 1 with every send. The function looks like that now
LastWorkTime = CALCULATE(SUM('Machine'[WorkTime]);LASTNONBLANK('Machine'[Mark2];1))

and it works fine, but why the one with [Date and Time] doesn't??

It will be much easier for the future if the function with Date and Time would work properly.