Forum Discussion

leandro_vegacr's avatar
leandro_vegacr
Helper I
2 years ago
Solved

Create calculation based on date hierarchy

Hello everyone.   I have a dataset that has, among others, these columns:     object_name uptime date measure obj1 95 1/12/2023 30 obj1 85 2/12/2023 30 obj2 91 1/12/2023 ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi leandro_vegacr 

    Here is my solution for you:

    First, create a measure that calculates the monthly average

     

     

    measure_per = var measured_objs = CALCULATE(COUNTROWS('Table'), 'Table'[uptime] > 90)
var normal_objs = CALCULATE(COUNTROWS('Table'), 'Table'[uptime] > 90, 'Table'[measure]>15)
return if(measured_objs > 0 && normal_objs > 0, DIVIDE(normal_objs, measured_objs, 0), 0)

     

    Then, you can choose one of these methods to calculate the daily average

     

    daily_per1 = var _table=SUMMARIZE('Table','Table'[date],"total_value",[measure_per])
return AVERAGEX(_table,[total_value])

    daily_per2 = AVERAGEX('Table',[measure_per])

     

     

    Here are the result

    Best Regards,

    Nono Chen

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi leandro_vegacr 

Here is my solution for you:

First, create a measure that calculates the monthly average

 

 

measure_per = var measured_objs = CALCULATE(COUNTROWS('Table'), 'Table'[uptime] > 90)
var normal_objs = CALCULATE(COUNTROWS('Table'), 'Table'[uptime] > 90, 'Table'[measure]>15)
return if(measured_objs > 0 && normal_objs > 0, DIVIDE(normal_objs, measured_objs, 0), 0)

 

Then, you can choose one of these methods to calculate the daily average

 

daily_per1 = var _table=SUMMARIZE('Table','Table'[date],"total_value",[measure_per])
return AVERAGEX(_table,[total_value])

daily_per2 = AVERAGEX('Table',[measure_per])

 

 

Here are the result

Best Regards,

Nono Chen

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

  • leandro_vegacr's avatar
    leandro_vegacr
    Helper I
    2 years ago

    Nono, thank you very much for looking into this, this looks great.

     

    Much appreciated!

  • leandro_vegacr's avatar
    leandro_vegacr
    Helper I
    2 years ago

    Nono, thank you very much for your help. Just for you to know, user changed the requirement so I'm now trying to understand what to do again, so I'm creating a new post. This one was solved greatly, just for you to know the one I'm creating is not because there is a flaw with this solution.

     

    Best