Forum Discussion
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 | 0 |
| obj2 | 80 | 2/12/2023 | 16 |
We get information from that table and display it in a data matrix. Measures are simple, for example, get distinct obj_names with an uptime greater than 90 (measure called: measured_objs), and also, from those, get objs which measure is greater than 15 (measure called normal_objs). Then, get the normal objs as a percentage of measured_objs.
This is fine. We display the info in a data table that has a date hierarchy; right now, measures are calculated by month, so, for example, with this data measured_objs would be 2 for Dec, and normal_objs would be 1 for Dec (only the first row). Finally, the avg would be 50 %.
However, we got another requirement: calculate that avg for each day, and then, get an avg of that daily avg. So, in this case, for Dec 1 measured_obj would be 2, normal_objs would be 1 and avg would be 50 % again, but for Dec 2 measured_obj would be 0, normal_obj would be 0 also and then avg would be 0. The new measure, averaging both days, would then return 25 %.
I'm not sure on how to write that measure. We got a slider that selects the month(s) to be displayed, and I just threw the measures in the datatable for them to be calculated based on the context of the datatable, which has month as the time dimension. If I expand I get the calculations for each day, but I won't get that new avg I need just from the measures I have, I think I need to write something else. How could I write something that summarizes the filtered columns in each case for each month?
Thanks a lot in advance.
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.
3 Replies
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.
Nono, thank you very much for looking into this, this looks great.
Much appreciated!
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
