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.

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.