Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chadmkelly
Regular Visitor

Creating a flag based on two measures

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:

chadmkelly_0-1695245827676.png

 

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:

chadmkelly_1-1695245967450.png

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 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
chadmkelly
Regular Visitor

@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
Regular Visitor

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!

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

chadmkelly_0-1695310799579.png

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?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

CoreyP
Solution Sage
Solution Sage

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors