Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to Solution.
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)
daily_per1 = var _table=SUMMARIZE('Table','Table'[date],"total_value",[measure_per])
return AVERAGEX(_table,[total_value])
daily_per2 = AVERAGEX('Table',[measure_per])
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.
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)
daily_per1 = var _table=SUMMARIZE('Table','Table'[date],"total_value",[measure_per])
return AVERAGEX(_table,[total_value])
daily_per2 = AVERAGEX('Table',[measure_per])
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!
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |