Help with Measure (division)

[benjamin_sasin]

Hi,

I'm doing okay with Power M Query, but DAX is just plain confusing to me.

I am trying to measure the conversion rate of my sales funnel:

Columns available are:

• [user_id] (the Identifier of the opportunity)
• [applicant_status_order] (Sales Pipeline Stage as an integer where the max is the won customer stage).

The formula I'm running is:

Conversion Rate = DIVIDE(CALCULATE(COUNT('Admissions'[user_id]),FILTER(ALLSELECTED(Admissions),Admissions[applicant_status_order] = 8)),CALCULATE(COUNT('Admissions'[user_id]),FILTER(ALLSELECTED(Admissions),Admissions[applicant_status_order] > 0)),0)

However, placing this measure on card visual returns 0.

How can I get this to be a % with a value?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[benjamin_sasin]

Hi there:

Measure 1 = CALCULATE(COUNT('Admissions'[user_id]),FILTER(ALLSELECTED(Admissions),Admissions[applicant_status_order] = 8))

>> Returns 28

Measure 2 = CALCULATE(COUNT('Admissions'[user_id]),FILTER(ALLSELECTED(Admissions),Admissions[applicant_status_order] > 0))

>> Returns 828

Conversion Rate = DIVIDE(CALCULATE(COUNT('Admissions'[user_id]),FILTER(ALLSELECTED(Admissions),Admissions[applicant_status_order] = 8)),CALCULATE(COUNT('Admissions'[user_id]),FILTER(ALLSELECTED(Admissions),Admissions[applicant_status_order] > 0)),0)

Which is basically equivalent to DIVIDE([Measure 1], [Measure 2], 0).

>>> Returns 0

I'm expecting 0.0338 or 3.38 %

[benjamin_sasin · Accepted solution]

Okay I just found the solution on https://stackoverflow.com/questions/38353064/how-to-format-new-measure-not-column-in-power-bi-as-per...

I needed to bracket this into a FORMAT( [my calculation], "0.00%").

[d_gosbell]

---

@benjamin_sasin wrote:

I needed to bracket this into a FORMAT( [my calculation], "0.00%").

---

I would not recommend doing this as this will convert your measure from a number to a string which limits the ways in which it can be used (you can't put string measures into charts that require numeric values which is nearly every chart type except for cards and tables)

A better solution is to click on the measure then on the "Measure Tools" (if you are using the old ribbon these options are in the "Modeling" tab)

And then click on the % option (and you can optionally change the number of decimal places using these formatting options also)

In this way the measure will retain it's numeric value and can be used in all types of charts and visuals, but when displayed as text it will use the specified format.