Forum Discussion

hamzashafiq's avatar
hamzashafiq
Icon for Kudo Collector rankKudo Collector
4 years ago
Solved

Need Help to built the formula

Hi All,

 

I have the below dataset. I want to calculate the usage, cost and usage/hour with the below formula and as per desired output. 

 

Usage= 

usage=calculate max-min where hours<>060-10=50
cost=calculate sum where hours<>0 and (index<>1 or index <> max(index))20+30+40+50+60=200
cost/hour=cost/usage200/50=40

The reason to use index in the formula is ; we don't want to include cost if the 0 in hours came in beginning(index 1) or end (max index). My formula works find, however when I calculate the cost/hour, it also exclude the cost on second row (30) despite adding the index condition. Please help me!

 

IndexHoursCost
11020
2030
34040
45050
56060
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi hamzashafiq 

    Thanks for your reply.

    >> we don't want to include cost if the 0 in hours came in beginning(index 1) or end (max index)

    In this scenario, you can try this,

    usage measure= MAXX('Table','Table'[Hours])-MINX(FILTER('Table','Table'[Hours]<>0),'Table'[Hours])
    cost measure = 
var _maxIndex= MAXX('Table','Table'[Index])
var _minIndex= MinX('Table','Table'[Index])
return SUMX(FILTER(ALL('Table'),NOT('Table'[Hours]=0 && ('Table'[Index]=_minIndex || 'Table'[Index]=_maxIndex))),'Table'[Cost])
    cost/hour = DIVIDE([cost measure],[usage measure])

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi hamzashafiq 

    Thanks for reaching out to us. According to your formula, the conditions on both sides of and must be met at the same time, so it will exclude the second line. It is recommended to use or, and then subdivide the original formula, for example (hours = 0 and...) or (hours<>0 and ...)

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

      • v-xiaotang's avatar
        v-xiaotang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi hamzashafiq 

        Thanks for your reply.

        >> we don't want to include cost if the 0 in hours came in beginning(index 1) or end (max index)

        In this scenario, you can try this,

        usage measure= MAXX('Table','Table'[Hours])-MINX(FILTER('Table','Table'[Hours]<>0),'Table'[Hours])
        cost measure = 
var _maxIndex= MAXX('Table','Table'[Index])
var _minIndex= MinX('Table','Table'[Index])
return SUMX(FILTER(ALL('Table'),NOT('Table'[Hours]=0 && ('Table'[Index]=_minIndex || 'Table'[Index]=_maxIndex))),'Table'[Cost])
        cost/hour = DIVIDE([cost measure],[usage measure])

         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.