Forum Discussion
Creating a flag based on two measures
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 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 )
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!
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?
Greg_Deckler above and beyond! Thanks, will look into this and see if I can get it to work for me.