Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am looking at percentage changes of patient volume from week to week.
I want to identify when an outlier arises so I calculated the mean and standard deviation. From there I used a z-value of -1.96 and 1.96 to capture 95% of the data. This gives me an upper and lower boundary.
A couple of issues right now:
1. When I drop the measures into a multi-line card everything looks perfect. But when I drop those same measures into a table it gets messed up and populates as the percent change.
Here are my formulas and proof they are measures:
2. I then want to create a flag that says "If the percent change is outside of the boundaries then populate it with a 1, otherwise populate with 0." From there I should be able to create a conditional format for percentages associated to a 1.
Any solutions as to why the measures get messed up when I drop them into a table?
Thank you for any help you can provide.
Solved! Go to Solution.
Figured it out, it was the FILTER ( AllSPs...) that was still bringing the row context along. Just need to change the measre to
ASP_Avg_Change = VAR PharmCode = "ASP" RETURN IF ( SELECTEDVALUE ( AllSPs[PharmCode] ) = PharmCode, CALCULATE ( AVERAGE ( AllSPs[PercentChange] ), ALL ( AllSPs ), AllSPs[PharmCode] = PharmCode ) )
I added the VAR PharmCode because the calc will show on all rows unless we check that we are on an ASP row.
We also need to update the ASP_Dev_Change the same way
ASP_Dev_Change = VAR PharmCode = "ASP" RETURN IF ( SELECTEDVALUE ( AllSPs[PharmCode] ) = PharmCode, CALCULATE ( STDEV.P ( AllSPs[PercentChange] ), ALL ( AllSPs ), AllSPs[PharmCode] = PharmCode ) )
Hello @holtw328
The are getting "messed up" by the other columns in the matrix that are filtering your calculation. If you take Weekend, patientdelta, and lag out of the matrix the should calc correctly because those things are no longer there to change the context in which the measure is evaluated. That is just to show you what is happening.
We can modify your measures to remove the filter context on everything but the PharmCode like so
ASP_Avg_Change = CALCULATE ( AVERAGE ( AllSPs[PercentChange] ), ALL ( AllSPs ), FILTER ( AllSPs, AllSPs[PharmCode] = "ASP" ) )
ASP_Dev_Change = CALCULATE ( STDEV.P ( AllSPs[PercentChange] ), ALL ( AllSPs ), FILTER ( AllSPs, AllSPs[PharmCode] = "ASP" ) )
Also, when referencing a measure in another measure you should never put the table name in front of the measure, it makes people think it is a column.
ASP_Lower = -1.96 * [ASP_Dev_Change] + [ASP_Avg_Change]
ASP_Upper = 1.96 * [ASP_Dev_Change] + [ASP_Avg_Change]
I went back and tried everything, and it works well, but it doesn't allow me to accomplish my specific goal. I need to identify the specific week in which the percent change goes outside of my boundaries. If I remove the weekend column I am not able to see which week needs to be reviewed.
Hello @holtw328
My note about removing the columns was just to show how those extra columns were impacting your old calculations. The updated measures should work even with those columns in the visual.
For some reason the upper and lower bounds are taking on the values of the percent change.
Here are the updated formulas and then the associated output. I may just try to do all of the calculations in SQL Server and then add a conditional column in the Query Editor. Like I said earlier, I'm a newbie with this stuff so part of the fun is learning the best/most efficient way of doing it.
Could you possible share your .pbix file?
Figured it out, it was the FILTER ( AllSPs...) that was still bringing the row context along. Just need to change the measre to
ASP_Avg_Change = VAR PharmCode = "ASP" RETURN IF ( SELECTEDVALUE ( AllSPs[PharmCode] ) = PharmCode, CALCULATE ( AVERAGE ( AllSPs[PercentChange] ), ALL ( AllSPs ), AllSPs[PharmCode] = PharmCode ) )
I added the VAR PharmCode because the calc will show on all rows unless we check that we are on an ASP row.
We also need to update the ASP_Dev_Change the same way
ASP_Dev_Change = VAR PharmCode = "ASP" RETURN IF ( SELECTEDVALUE ( AllSPs[PharmCode] ) = PharmCode, CALCULATE ( STDEV.P ( AllSPs[PercentChange] ), ALL ( AllSPs ), AllSPs[PharmCode] = PharmCode ) )
You're the best. Can't begin to thank you enough. Hopefully I can learn Power BI as well as you and then help others out in the future.
All the best.
Thanks @holtw328 . If you would, mark this one as solved so the Microsoft folks know you got your answer.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |