Calculate conversions as a measure

[enorberg]

Pretty new to Power BI so hoping for some help on creating a measure.

I have a table (Engagement) of sales activity data, consisting of one row per activity. I'd like to divide the number of meetings booked (engagement_type = MEETING) by the number of calls (engagement_type = CALL) on any given day, creating a daily conversion rate measure. How would I go about doing that?

[v-yiruan-msft · Accepted solution]

Hi @enorberg ,

If you have the same structure with the table in below screenshot, you can create one similar measure using DIVIDE function  just like this:

Ratio = var nm=CALCULATE(DISTINCTCOUNT('Engagement'[Index]),FILTER(ALL('Engagement'),'Engagement'[Engagement_type]="MEETING"&&'Engagement'[Engagement Date]=SELECTEDVALUE('Engagement'[Engagement Date])))  //get the number of meetings on a specific date
var nc=CALCULATE(DISTINCTCOUNT('Engagement'[Index]),FILTER(ALL('Engagement'),'Engagement'[Engagement_type]="CALL"&&'Engagement'[Engagement Date]=SELECTEDVALUE('Engagement'[Engagement Date])))  //get the number of calls on a specific date
return DIVIDE(nm,nc)

If the above measure is not applicable in your scenario, please provide the related table structure and more sample data(exclude sensitive data).

Best Regards

Rena

[v-yiruan-msft · Accepted solution]

Hi @enorberg,

I don't know if you noticed there was a condition in that formula: 'Engagement '[Engagement Date] = SELECTEDVALUE (' Engagement '[Engagement Date])... The SELECTEDVALUE (' Engagement '[Engagement Date]) corresponds to the date selected by the slicer in the screenshot in my previous post. There is no slicer in your screenshot, which means no returned value. Hence there is no data display in the visual... In addition, I found there is one error about your formula. We need to count the number of meetings not the number of meeting type, so you need to update the formula from DISTINCTCOUNT('Engagement'[engagement_type]) to 'DISTINCTCOUNT('Engagement'[engagementid]) . Please complete the steps in below screenshot:

1. Add one slicer with Field "engagement_date"

2. Update the formula as below:

Ratio = var nm=CALCULATE(DISTINCTCOUNT('Engagement'[engagementid]),FILTER(ALL('Engagement'),'Engagement'[engagement_type]="MEETING"&&'Engagement'[engagement_date]=SELECTEDVALUE('Engagement'[engagement_date]))) 

var nc=CALCULATE(DISTINCTCOUNT('Engagement'[engagementid]),FILTER(ALL('Engagement'),'Engagement'[engagement_type]="CALL"&&'Engagement'[engagement_date]=SELECTEDVALUE('Engagement'[engagement_date])))

return DIVIDE(if(ISBLANK(nm),0,nm),if(ISBLANK(nc),1,nc))

The following documentations describe the details of these functions applied in the formula:

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

https://docs.microsoft.com/en-us/dax/selectedvalue-function

https://docs.microsoft.com/en-us/dax/filter-function-dax

https://docs.microsoft.com/en-us/dax/divide-function-dax

Best Regards

Rena

[Anonymous]

Hi @enorberg,

You can use a calculated measure that would look like this:

DIVIDE(CALCULATE(SUM([FIELD]),[TYPEFIELD]="MEETING"),CALCULATE(SUM([FIELD]),[TYPEFIELD]="CALL"),0)

This would divide the sum filtered by the type Meeting for the sum filtered by the type call.

Let me know if it helped.

Best Regards,

DR 

[enorberg]

HI @Anonymous ,

Many thanks for your help and apologies for the late reply.

I just tried your solution and ran into some trouble. What is the difference between 'FIELD' and 'TYPEFIELD' in your example? I've successfully replaced 'FIELD' with 'engagement_type' but didn't manage with 'TYPEFIELD' (error). What am I missing?

Thanks 🙂

[v-yiruan-msft · Accepted solution]

Hi @enorberg ,

If you have the same structure with the table in below screenshot, you can create one similar measure using DIVIDE function  just like this:

Ratio = var nm=CALCULATE(DISTINCTCOUNT('Engagement'[Index]),FILTER(ALL('Engagement'),'Engagement'[Engagement_type]="MEETING"&&'Engagement'[Engagement Date]=SELECTEDVALUE('Engagement'[Engagement Date])))  //get the number of meetings on a specific date
var nc=CALCULATE(DISTINCTCOUNT('Engagement'[Index]),FILTER(ALL('Engagement'),'Engagement'[Engagement_type]="CALL"&&'Engagement'[Engagement Date]=SELECTEDVALUE('Engagement'[Engagement Date])))  //get the number of calls on a specific date
return DIVIDE(nm,nc)

If the above measure is not applicable in your scenario, please provide the related table structure and more sample data(exclude sensitive data).

Best Regards

Rena

[enorberg]

Hi @v-yiruan-msft ,

Many thanks for trying to help. I've been working on using your solution, but it doesn't seem to work:

Here is some sample data (displaying calls only):

What am I doing wrong here? Thanks

[v-yiruan-msft · Accepted solution]

Hi @enorberg,

I don't know if you noticed there was a condition in that formula: 'Engagement '[Engagement Date] = SELECTEDVALUE (' Engagement '[Engagement Date])... The SELECTEDVALUE (' Engagement '[Engagement Date]) corresponds to the date selected by the slicer in the screenshot in my previous post. There is no slicer in your screenshot, which means no returned value. Hence there is no data display in the visual... In addition, I found there is one error about your formula. We need to count the number of meetings not the number of meeting type, so you need to update the formula from DISTINCTCOUNT('Engagement'[engagement_type]) to 'DISTINCTCOUNT('Engagement'[engagementid]) . Please complete the steps in below screenshot:

1. Add one slicer with Field "engagement_date"

2. Update the formula as below:

Ratio = var nm=CALCULATE(DISTINCTCOUNT('Engagement'[engagementid]),FILTER(ALL('Engagement'),'Engagement'[engagement_type]="MEETING"&&'Engagement'[engagement_date]=SELECTEDVALUE('Engagement'[engagement_date]))) 

var nc=CALCULATE(DISTINCTCOUNT('Engagement'[engagementid]),FILTER(ALL('Engagement'),'Engagement'[engagement_type]="CALL"&&'Engagement'[engagement_date]=SELECTEDVALUE('Engagement'[engagement_date])))

return DIVIDE(if(ISBLANK(nm),0,nm),if(ISBLANK(nc),1,nc))

The following documentations describe the details of these functions applied in the formula:

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

https://docs.microsoft.com/en-us/dax/selectedvalue-function

https://docs.microsoft.com/en-us/dax/filter-function-dax

https://docs.microsoft.com/en-us/dax/divide-function-dax

Best Regards

Rena

[enorberg]

Thank you so much @v-yiruan-msft , it worked 🙂 I had actually already added the slicer without success, but after correcting the code it went from blank to filled.

Is there a way I can look at ratio over time using this measure? It will only show the ratio for specific dates, not multiple dates (when I select multiple dates it returns 0). Ideally, I'd like to see how this ratio changes over time.

[v-yiruan-msft · Accepted solution]

Hi @enorberg ,

Please try to update the formula as below:

Ratio = var nm=CALCULATE(DISTINCTCOUNT('Engagement'[engagementid]),FILTER(ALL('Engagement'),'Engagement'[engagement_type]="MEETING"&&'Engagement'[engagement_date] in VALUES('Engagement'[engagement_date]))) 

var nc=CALCULATE(DISTINCTCOUNT('Engagement'[engagementid]),FILTER(ALL('Engagement'),'Engagement'[engagement_type]="CALL"&&'Engagement'[engagement_date] in VALUES('Engagement'[engagement_date])))

return DIVIDE(if(ISBLANK(nm),0,nm),if(ISBLANK(nc),1,nc))

Best Regards

Rena

[enorberg]

Thank you @v-yiruan-msft, this really helped 🙂 Just to make sure I understand, this calculates the daily ratio, right? Not the accumulated ratio at any given day?