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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
leandro_vegacr
Frequent Visitor

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors