Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I am trying to write a measure that calculates yoy change by property. I am filtering out yoy’s that are beyond a threshold because there is probably something odd going on with those yoy’s values. Once I blank out yoy’s beyond a threshold I am taking an average by property. That all seems to be working fine. At the total level I want the average of the averages.
I would like to do all this in one measure using variables but I cant figure out why I need to create a new measure to get the desired answer on the total line.1.10% is the average of non-blanked yoy values by property.
How come this last step does not work in my 1step measure but does work if I do the simple averagex of the previous measure (2step measure)?
Hi @mgrayTCB ,
For _NRRI YOY Ave % 1step, it defines a threshold _threshold and then uses the SUMMARIZE and AVERAGEX functions combined with an if statement to conditionally compute the average based on the threshold. This function calculates the annual average within the threshold.
For _NRRI YOY Ave % 2Steps, which does not have any constraints, the AVERAGEX function is used directly to compute the annual average of the attribute name.
In summary, the first function excludes certain values by introducing conditional logic, possibly to deal with outliers or specific data points. The second function, on the other hand, calculates the average of all given conditions. This difference in calculation methods may result in different final averages.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am not following your argment. Unless I am missing something both the 1step and the 2steps measures work off the same logic in NRRI YOY%. In the 1step measure I think I am just taking the last variable and _AverageWithinThreshold and avering it by property. In the 2step measure I am just refereincing the NRRI YOY% measure and doing the same thing that the result is doing in the 1Step measure but ending up with different answers in the total row and cant figure out why.
Here is a simpler example of the same problem (or more likely something fundimental that I am missing). In the table below 47.5% is the average of the average of each property and the answer I am trying to get without writing two measures and having the second one reference the first one. What am I mssing?
-----------
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |