cancel
Showing results for
Did you mean:
Resolver I

## Help with Measure (division)

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?

1 ACCEPTED SOLUTION
Resolver I

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%").

4 REPLIES 4
Super User III

Hi,

Share some data and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

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 %
Resolver I

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%").

Super User II

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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors