cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Conditional Format off of measure

@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
Highlighted
Super User IV
Super User IV

Re: Conditional Format off of measure

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]

 

Highlighted
Regular Visitor

Re: Conditional Format off of measure

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.
Highlighted
Regular Visitor

Re: Conditional Format off of measure

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.

Highlighted
Super User IV
Super User IV

Re: Conditional Format off of measure

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.

Highlighted
Regular Visitor

Re: Conditional Format off of measure

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
 

 

 

Highlighted
Super User IV
Super User IV

Re: Conditional Format off of measure

Could you possible share your .pbix file?

Highlighted
Super User IV
Super User IV

Re: Conditional Format off of measure

@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

Highlighted
Regular Visitor

Re: Conditional Format off of measure

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.

Highlighted
Super User IV
Super User IV

Re: Conditional Format off of measure

Thanks @holtw328 .  If you would, mark this one as solved so the Microsoft folks know you got your answer.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors