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