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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
holtw328
Regular Visitor

Conditional Format off of measure

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:

 

ASP_Avg_Change = CALCULATE(AVERAGE(AllSPs[PercentChange]),FILTER(AllSPs,AllSPs[PharmCode]="ASP"))
ASP_Dev_Change = CALCULATE(STDEV.P(AllSPs[PercentChange]),FILTER(AllSPs,AllSPs[PharmCode]="ASP"))
ASP_Lower = -1.96*AllSPs[ASP_Dev_Change]+AllSPs[ASP_Avg_Change]
ASP_Upper = 1.96*AllSPs[ASP_Dev_Change]+AllSPs[ASP_Avg_Change]

 

 

Capture.PNG

 

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?PowerBI_CondFormat.PNG

 

Thank you for any help you can provide.

1 ACCEPTED SOLUTION

@holtw328 ,

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

AvgASPPctChange.jpg

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

View solution in original post

9 REPLIES 9
jdbuchanan71
Super User
Super User

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.

 

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" )
)
 
ASP_Lower = -1.96*[ASP_Dev_Change]+[ASP_Avg_Change]
 
ASP_Upper = 1.96*[ASP_Dev_Change]+[ASP_Avg_Change]
 
Capture2.PNGCapture3.PNG
 

 

 

Could you possible share your .pbix file?

@holtw328 ,

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

AvgASPPctChange.jpg

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.

Thank you for the through, detailed and quick reply. I especially appreciated the tips along the way. This is the first time I’ve worked with a visual platform like Power BI so the extra insight helps a lot.

I’ll try running everything when I get back to the office.

Thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.