Create calculation based on date hierarchy

[leandro_vegacr]

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. 

[v-nuoc-msft · Accepted solution]

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]

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

[leandro_vegacr]

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

Much appreciated!