Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Undefined

I have stop name , boarding & alighting columns in my table. Consider the below example. Boarding means passengers getting into the bus & Alighting means passengers get down the bus. From A to B 10-0 = 10 passengers travelled. B to C 10+7-5 = 12 passengers travelled. C to D 12+8-6 = 14 passengers travelled. From D to E 14+5-3=16 passengers travelled.

Logic for B to C is : Previous stop passengers travelled from(A to B) + current stop boarding(B)-current stop alighting(B)

Logic for C to D is : Previous stop passengers travelled from(B to C) + current stop boarding(C)-current stop alighting(C)

The above logic is same for all in between stops (A to B), (B to C), (C to D), (D to E)......etc

Based on this logic pls provide me DAX to calculate no of passengers travelled between each stop (Dynamic DAX) A,B,C,D,E are the stops. stop_name, boarding, alighting

A,10, 0

B ,7, 5

C, 8, 6

D, 5 ,3

E ,0, 16

 

The output should be like this 

 

A to B :  10

B to C :  12

C to D :  14

D to E :  16

 

 

A to B -9

B to C - 16

C to D - 24

D to E - 26

6 Replies

  • Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks Ahmedx .  I have more than 20000 rows(travelled column in the pbix file you have shared). How to add this column in my table? Please suggest me

    • Ahmedx's avatar
      Ahmedx
      Super User

      do you mean column index? you can add it to the power request

      • Anonymous's avatar
        Anonymous
        Not applicable

        In my data I have duplicate values( Considering you pbix file passengers travelled column has duplicate vaalues)