Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 -...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Anonymous ,

    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
    )
)
jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper User
7 years ago

Hello Anonymous 

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]

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago
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.