Forum Discussion

viferenc's avatar
viferenc
Icon for Helper II rankHelper II
5 years ago
Solved

Sum with specific criteria

Dear Community,

 

I would need your support.

 

Here you can see a snapshot from the table.

 

The refuel of the cars is not always at the same time as the route starts or ends. It could happen the refuel is only after several route.

 

I would need a support how I can sum up the KM between two refuel the in the column "correct KM for liter/km", marked yellow and green, only if I have the summed up KM can I calculate the correct liter/km, marked blue and yellow and this avg. liter/km I would like to show in the related route.

 

This logic should work for all licence number.

 

I hope I was somehow understandable

 

If you have a different/better solution I´m open for it.

 

Thank you for your support.

 

Feri

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    viferenc

    here is a workaround for you.

    1. create an index column in PQ

    2. use dax to create a column

    Column = 
VAR lastindex=MAXX(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<EARLIER('Table'[Index])&&NOT(ISBLANK('Table'[Refuel]))),'Table'[Index])
return if(ISBLANK('Table'[Refuel]),blank(),if(ISBLANK(lastindex),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Distance KM]),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>lastindex),'Table'[Distance KM])))

    However, I am still not clear about the logic of last column in ur screenshot.

    please see the attachment below

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    viferenc

    here is a workaround for you.

    1. create an index column in PQ

    2. use dax to create a column

    Column = 
VAR lastindex=MAXX(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<EARLIER('Table'[Index])&&NOT(ISBLANK('Table'[Refuel]))),'Table'[Index])
return if(ISBLANK('Table'[Refuel]),blank(),if(ISBLANK(lastindex),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Distance KM]),sumx(FILTER('Table','Table'[licence number]=EARLIER('Table'[licence number])&&'Table'[Index]<=EARLIER('Table'[Index])&&'Table'[Index]>lastindex),'Table'[Distance KM])))

    However, I am still not clear about the logic of last column in ur screenshot.

    please see the attachment below

    • viferenc's avatar
      viferenc
      Icon for Helper II rankHelper II
      5 years ago

      Thank you very much.

       

      It works perfec.t

       

      With my last column I calculate the avg consumption (avg L/100km).  And this avg consumption, which is calculated in Index 3 (32,03 l/100km) I would like to show for Index 2 and Index 1, because it is related to them also. (in the same or in an extra column)

      The same should work also for Index 4, the avg consunsuption (33,53 l/100km) which is calculated for Index 5, should be shown in index 4

       

       

      I´m really appriciate for your support, it is a huge support.

       

      Thank you 

       

      Feri