Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculation over rows with condition

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! :smileyhappy:

 

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! :smileywink:

General Comment
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous

       

      Sure, here it is:

       

      Sample

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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

  • JosePintor's avatar
    JosePintor
    Frequent Visitor
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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