cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benjamin_sasin
Resolver I
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

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

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User III
Super User III

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 %

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

View solution in original post


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

2020-01 measure tools.png

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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 Kudoed Authors