Calculation over rows with condition

[Anonymous]

Hello all.

I am trying to improve a file I have worked on. I need some calculations over rows with a condition, and I was not able to do them on the interface (DAX), so I created different tables on the query and I worked on them. The file works and the values are good, but I would like to do it properly! 

So, I have a table with trip data:

Let me explain its logic: each vehicle does only one total trip per day, comprised of several small trips.

I need to calculate (per total trip); stop time, number of stops and return time.

I'll show you the key steps for each calculation I performed on the query for each value.

- Stop time: I created two indexes and merged the queries to get consecutive values, then I added a conditional column to check if the vehicle and date are the same. If they are, it is a stop time.

- Number of stops: I grouped the rows by date and vehicle, counted the rows and calculated the number of stops.

- Return time: similar as Stop time, only changed the condition; if the vehicle is the same but the date is different, then it is the final trip.

As I told you, what I did works but I know it's not the brightest thing to do.

Can you please help me on this quest to nice data? Have you worked or can you show me similar projects?

Thanks a lot in advance, cheers! 

[Anonymous]

Hi,

Can some help on my following requirement - 

1. I need to show a table with Idle time of ATM machine when no deposit or withdrawal happened against that machine . There are hundredes of machine  and I need to add a column in a table and populate it with idle time Like this . What dax formual or measure I need to use 

2. Also I need the counts of amchine which are idle through a measure which I can use in Power Pivot

[JosePintor]

Hi everybody,

I have a similar issue with calculate GrandTotal over a TOPN list that changes with drilldown of 4 dimensions.

how can i SUM only the visibles TOP5 values refering to the correspondent dimension at visible drilldown level.

Let's explain:

I have a matrix with 4 dimensions with drilldown (TipoArtigo; Marca; Familia; Artigo)

I want to put the totals in the middle column (wich is a measure of TOPN margin of the dimension that is in each level of drill down. Basically I need to have only one measure that works for all 4 drill downs dimensions.

I tried with SUMX and TOPN to achive Totals only for TOPN of each dimension, but I couldn’t be able to achieve that. The problem is that in totals we don’t have row context and don’t know what dimension is in matrix lines at each moment, to sum only TOPN (5 in case) for that dimension.

I have 4 measures (one for each dimension) that work ok only when matches with the dimension in drilldown, that's why I need a meaures for all dimensions.

1st column (TOPN HierarArtigo Margem) works ok in all dimensionsm but give the GrandTotal fo all the items and not only the visibles ones (TOP 5).

2nd column column (TOPN HierarArtigo Margem Parcial)  is the one o don’t work on totals only for TOP 5 itens.

3rd  column  (TOPN <dimension _name> Margem Parcial) work ok only with the corresponding dimension in lines otherwise gives only total (and wrong, because it’s the total that TOPN is supposed to calc based on the specific data_column).

In the follows print screens the 1st and 2nd measures area the same for all drilldowns, the 3rd changes….

Don’t worried because the grand total of the first column is lower than the parcial totals, because there are items with negative values (margin).

At the end I post all those measures.

How can I SUMX just the visibles rows in matrix depending on drilldown level selected by user, that changes the TOPN ( VALUES ( column) ) ?!

Thanks for help!

Regards,

José Pintor

[v-shex-msft]

Hi @Anonymous,

Based on your description, it sounds like compare current records and previous records.

Can you please share some sample data for further test and analysis?

You can also take a look at below link which has the similar requirement:

Recording Daily Data

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft

Sure, here it is:

Sample

[v-shex-msft]

Hi @Anonymous,

Can you please share pbix/excel file with some fake data? I will test on it.(it is trouble to manual input your data from the screenshots)

Regards,

Xiaoxin Sheng

[Anonymous]

Sorry @v-shex-msft, I've been away.

Here is the file.

https://www.dropbox.com/s/wnhwj2ch1r5cfia/Test.pbix?dl=0

Thank you!

[v-shex-msft]

Hi @Anonymous,

You can take a look at below formulas if they suitable for your requirement.

Measures:

Number of Stop = 
CALCULATE (
    COUNT ( [VehicleID] ) - 1,
    FILTER (
        ALLSELECTED ( 'Trips' ),
        [VehicleID] = MAX ( [VehicleID] )
            && [TripStartDate] = MAX ( [TripStartDate] )
            && [Date] = MAX ( [Date] )
    )
)

Stop time = 
var _rangeEnd=MAX(Trips[TripStartDateTime])
VAR started =
    CALCULATE (
        MAX ( [TripEndDateTime] ),
        FILTER (
            ALL ( Trips ),
            [VehicleID] = MAX( [VehicleID] )
                && [Date] = MAX ( [Date] )
                && [TripEndDateTime] < _rangeEnd
        )
    )
RETURN
    DATEDIFF ( started,_rangeEnd, SECOND ) + 0

Return time = 
VAR _lastStart =
    CALCULATE (
        MAX ( Trips[TripStartDateTime] ),
        FILTER (
            ALLSELECTED ( Trips ),
            [VehicleID] = MAX ( [VehicleID] )
                && [Date] = MAX ( Trips[Date] )
        )
    )
VAR _lastEnd =
    CALCULATE (
        MAX ( Trips[TripEndDateTime] ),
        FILTER (
            ALLSELECTED ( Trips ),
            [VehicleID] = MAX ( [VehicleID] )
                && [Date] = MAX ( Trips[Date] )
        )
    )
RETURN
    IF (
        DATEVALUE ( _lastStart ) <> DATEVALUE ( _lastEnd ),
        DATEDIFF ( _lastStart, _lastEnd, SECOND ),
        DATEDIFF ( _lastEnd, DATEVALUE ( _lastEnd ) + 1, SECOND )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

@v-shex-msft

That's impressive. I dindn't know that logic for grouping with criteria. That's awesome

For Number of Stops and Return Time, it works perfectly, as it grabs one value only.

However, for the Stop Time, it doesn't, because the calculation should somehow group (sum) all the partial stop times for each trip. With the Measure you proposed, I think we only get one value.

So, for each trip we should do the time difference between the connected values (diagonally) and sum them,

Is it possible to calculate the total Stop Time?

Thanks again, this is something I'll definitely use on future projects.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

If you want to get the summaried stop time you can try to add new measure with below formula:

Stop time(Summaried) =
VAR temp =
    ADDCOLUMNS (
        ALL ( Trips ),
        "Stoped Time", LOOKUPVALUE (
            Trips[TripEndDateTime],
            Trips[VehicleID], [VehicleID],
            Trips[Date], [Date],
            Trips[Index], [Index] - 1
        )
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin Sheng

I am new to BI . I have simlar requirement wherein need ot get ATM idletime in a column against ATM Machine wherein no deposit or withdrawal hapened during operations time . For example 1:00 Am to 4 AM if there  is no deposit or withdrawal then the column is populated with no. of hours smachine remained idle i.e no cash movement 

Also I need a measure to get number of all such idle machines . 

Can you help for both requirements ?

 Pls see the image 

[Anonymous]

@v-shex-msft

Again, impressive and straight to the point. It works flawlessly!

Anyway, I didn't get one bit of the code:

SUMX (
        ADDCOLUMNS (
            FILTER ( temp, [VehicleID] = MAX ( [VehicleID] ) && [Date] = MAX ( [Date] ) ),
            "Diff", DATEDIFF ( [Stoped Time], [TripStartDateTime], SECOND )
        ),
        [Diff]
    )

 If we filter according to Vehicle ID and Date, which makes perfect sense, I would expect the first "Stopped Value" to be wrong, as it comes from the previous entry (i.e. a different Date or Vehicle), but it doesn't happen to be like this, which is good for our purpose, but I don't get it. Let me show as if we did it in a table ourselves:

VehicleID TripStartDateTime TripEndDateTime Index Stopped Time

2585	02/10/2017 12:20:39	02/10/2017 12:23:04	1	NA
2585	02/10/2017 13:07:25	02/10/2017 13:13:36	2	02/10/2017 12:23:04
2585	03/10/2017 12:31:09	03/10/2017 12:37:25	3	02/10/2017 13:13:36
2585	03/10/2017 12:41:01	03/10/2017 12:44:56	4	03/10/2017 12:37:25
2585	03/10/2017 12:56:03	03/10/2017 13:01:31	5	03/10/2017 12:44:56
2585	03/10/2017 13:02:52	03/10/2017 13:07:28	6	03/10/2017 13:01:31
3815	02/10/2017 12:20:00	02/10/2017 12:24:17	7	03/10/2017 13:07:28
3815	02/10/2017 12:26:40	02/10/2017 12:34:41	8	02/10/2017 12:24:17
3815	02/10/2017 12:36:56	02/10/2017 12:44:22	9	02/10/2017 12:34:41
3815	02/10/2017 13:04:37	02/10/2017 13:12:54	10	02/10/2017 12:44:22

To finalize this topic, where am I failing on the logic?

Thanks again! 

[v-shex-msft]

Hi @Anonymous,

>> I would expect the first "Stopped Value" to be wrong, as it comes from the previous entry (i.e. a different Date or Vehicle), but it doesn't happen to be like this, which is good for our purpose, but I don't get it.

Actually, you can use current date as filter condition to simply avoid this situation.

The bolded part is the conditions, I used current vehicle ID and current date as the filter.(Some of summary functions can used to get current row contents)

Regards,

Xiaoxin Sheng