Calculate and display ratio ( eg 1 : 2 )

[KRISH80]

Dear All,

I need to claculate number of call to number of opportunity ratio. i.e. if i have made 10 calls to a customer and those calls generated 5 oppotunities, my calls to opportunity ratio is ( 10/5 = 2) 1:2. 

I wanted to know how to display the ratio 1:2 , what are the fomratting OR DAX i should use ?

I dn't want to display "2" instaed i want to display "1:2".

Kindly help.

Regards,

Krishna

[Greg_Deckler · Accepted solution]

Well, you could return "1:" & [num]. Or if you want to keep it as a number under the hood, you could use the technique found here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[Anonymous · Accepted solution]

You have to calculate 2 measures:

[Number of Calls]
[Number of Opportunities]

Then you have to find the GCD using the GCD DAX function and then divide both by this GCD and then create such a measure:

[Your Final measure] =
var __calls = [Number Of Calls]
var __opps = [Number of Opportunities]
var __gcd = GCD( __calls, __opps )
var __result =
	COMBINEVALUES(
		":", // delimiter
		int(__opps / __gcd ),
		int(__calls / __gcd )
	)
return
	__result

Best

D

[KRISH80]

@Anonymous  thanks a lot. But what i am getting with your DA is shown in table below.

What i need is, for eg - i have had 49 meetings with HDFC and generated 9 deals. So my meeting to deal ratio is =DIVIDE (49,9) and show 5:1. that means i have to have 5 meetings to generate 1 deal.

Account	Total Meeting	# of Deals	Calls to Deal Ratio	Actualy it should show
HDFC	49	9	9:49	5:1

[Anonymous]

You are not stating your problems clearly.

First off, 9 does not go into 49 evenly. Secondly, 49:9 = 5.44:1 = 544:100 = 136:25. You have not stated what it really is you want. Based on what I've given you, hopefully you can build your own solution.

Best
D

[KRISH80]

@Anonymous Thanks a lot

[Greg_Deckler · Accepted solution]

Well, you could return "1:" & [num]. Or if you want to keep it as a number under the hood, you could use the technique found here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[KRISH80]

Thanks a lot Greg.