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<>0 60-...
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.

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.

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

Thanks v-xiaotang could you please send me the updated formula, I am still confused.

  • 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.