Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

Create calculation based on date hierarchy

Hello everyone.

 

I have a dataset that has, among others, these columns:

 

 

object_nameuptimedatemeasure
obj1951/12/202330
obj1852/12/202330
obj2911/12/20230
obj2802/12/202316

 

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. 

1 ACCEPTED SOLUTION
v-nuoc-msft
Community Support
Community Support

Hi @leandro_vegacr 

Here is my solution for you:

First, create a measure that calculates the monthly average

vnuocmsft_1-1701944736757.png

 

vnuocmsft_2-1701944745703.png

 

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

vnuocmsft_3-1701944830229.png

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.

View solution in original post

3 REPLIES 3
v-nuoc-msft
Community Support
Community Support

Hi @leandro_vegacr 

Here is my solution for you:

First, create a measure that calculates the monthly average

vnuocmsft_1-1701944736757.png

 

vnuocmsft_2-1701944745703.png

 

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

vnuocmsft_3-1701944830229.png

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 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

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

 

Much appreciated!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.