Creating a flag based on two measures

[chadmkelly]

Hi, I have a measure that calculates the cost per lead, within a table by client id. 

I also have a measure that shows the std deviation and mean of the cost per lead for the entire cohort I'm looking at.

I want to create a flag that shows me when the CPL of the client is above the (std dev .+ mean) calculation.

the CPL and other measures (STD + Mean) will change based on the vertical selected, the timeframe, etc - that flag needs to work based on all the filters. to show some sample data based on what I'm after:

cost per lead within a table:

I want to then flag (or highlight) those CPLs in the table if they are above these metrics which are cards, based on the entire cohort:

in this case the $14.10 would be flagged, or highlighted red (in the table) since it's above the $10.22.

thanks for any assistance.

@Greg_Deckler 

[chadmkelly]

@CoreyP @Greg_Deckler of course the follow request comes from the team:) I'm now looking for a way to provide the standard deviation across similar clients. For example: I want the SD of my cost per lead for the following:

• within the past 30 days
• and for clients with the same vertical

As an example if the SD for the clients in "Auto Dealer" for the past 30 days is $50, I want to flag those clients in the same "Auto" vertical that have a SD above $50.  Then I would have the flag work differently for client's in the "Auto Mechanic" vertical since their SD over the past 30 days would be different, say $75, I would want all the client's within the "auto mechanic" vertical above $75 to be flagged.

I thought I could modify this somehow, I updated my cpl measure to bring back the cpl of the past 30 days, but I'm not having any luck figuring out how to bring in the vertical filter. 

standard_deviation =
VAR __table = SUMMARIZE(ALLSELECTED('dataset'),'dataset'[client_id],"__Measure",[cost_per_lead_last_30])
RETURN STDEVX.P(__table,[__Measure])

thanks for any assistance!

[chadmkelly]

yeah, for the std dev and mean/avg cards:

standard_deviation =

VAR __table = SUMMARIZE('dataset','dataset'[client_id],"__Measure",[cost_per_lead])

RETURN STDEVX.P(__table,[__Measure])
average_cpl = 

VAR __table = SUMMARIZE('dataset','dataset'[clientid],"__Measure",[cost_per_lead])

RETURN AVERAGEX(__table,[__Measure])
std_dev + Avg = 

CALCULATE([average_cpl]+[standard_deviation])

then for the Cost per lead that I use in the table:
cost per lead: 

DIVIDE([measure_monthly_billing],[leads]))

thanks!

[Greg_Deckler · Accepted solution]

@chadmkelly I'm going to agree with @CoreyP on this. It's likely that you are going to have to remove whatever context (filters) you have within your table visual in order to get this to work. So something like this:

standard_deviation =
VAR __table = SUMMARIZE(ALLSELECTED('dataset'),'dataset'[client_id],"__Measure",[cost_per_lead])
RETURN STDEVX.P(__table,[__Measure])
average_cpl = 
VAR __table = SUMMARIZE(ALLSELECTED('dataset'),'dataset'[clientid],"__Measure",[cost_per_lead])
RETURN AVERAGEX(__table,[__Measure])
std_dev + Avg = 
CALCULATE([average_cpl]+[standard_deviation])

Then you *should* be able to create a complex selector like this ( you can use it in your Filters pane or adjust to return an icon (UNICHAR).

Selector = IF( [Date Range - Cost Per Lead] > [std_dev + Avg], 1, 0 )

[chadmkelly]

@Greg_Deckler @CoreyP  really appreciate the help, I got this to work in my sample data:

putting it in my actual report is causing some performance issues looks like, as it's just spinning (for the last 10 min) - that's probably separate issue, but thank you for helping me through this!

[Greg_Deckler]

@chadmkelly You might be able to improve performance doing something like the following:

standard_deviation =
VAR __table1 = ALLSELECTED('dataset')
VAR __table = SUMMARIZE(__table1,'dataset'[client_id],"__Measure",[cost_per_lead])
VAR __result = STDEVX.P(__table,[__Measure])
RETURN 
  __result

average_cpl = 
VAR __table1 = ALLSELECTED('dataset')
VAR __table = SUMMARIZE(__table1,'dataset'[clientid],"__Measure",[cost_per_lead])
VAR __result = AVERAGEX(__table,[__Measure])
RETURN 
  __result

std_dev + Avg = 
[average_cpl]+[standard_deviation]

Of course, the issue may also be with your [cost_per_lead] measure. At least this way you might be able to troubleshoot which steps is causing a performance issue as you can change your return statement to do something like TOCSV(__table1) and step through to potentially figure out which step is being slow.

What does cost per lead measure look like?

[chadmkelly]

@Greg_Deckler above and beyond! Thanks, will look into this and see if I can get it to work for me.

[CoreyP]

Can you please share your DAX measures? I'm pretty sure you just need to remove the row level context of the client ID by using ALLSELECTED.