topic Re: Create calculation based on date hierarchy in DAX Commands and Tips

Create calculation based on date hierarchy

leandro_vegacr — Tue, 05 Dec 2023 19:58:40 GMT

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.

Re: Create calculation based on date hierarchy

Anonymous — Thu, 07 Dec 2023 10:28:04 GMT

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.

Re: Create calculation based on date hierarchy

leandro_vegacr — Thu, 07 Dec 2023 13:42:31 GMT

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

Much appreciated!

Re: Create calculation based on date hierarchy

leandro_vegacr — Thu, 07 Dec 2023 17:14:31 GMT

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