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
mgrayTCB
Helper III
Helper III

Measure variable not working as expected

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

 

mgrayTCB_0-1715785444245.png

 

mgrayTCB_1-1715785485954.png

 

mgrayTCB_2-1715785528912.png

 

mgrayTCB_3-1715785557526.pngmgrayTCB_4-1715785590955.png

 

 

3 REPLIES 3
v-yilong-msft
Community Support
Community Support

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?

mgrayTCB_0-1715863935367.png

-----------

mgrayTCB_1-1715863989628.png

 

mgrayTCB_2-1715864036006.png

mgrayTCB_3-1715864081742.png

 

mgrayTCB_4-1715864129948.png

 

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.