topic Re: Calculate and display ratio ( eg 1 : 2 ) in DAX Commands and Tips

Calculate and display ratio ( eg 1 : 2 )

KRISH80 — Mon, 04 May 2020 08:56:35 GMT

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

Re: Calculate and display ratio ( eg 1 : 2 )

Greg_Deckler — Mon, 04 May 2020 11:50:10 GMT

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

Re: Calculate and display ratio ( eg 1 : 2 )

Anonymous — Mon, 04 May 2020 12:53:04 GMT

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

Re: Calculate and display ratio ( eg 1 : 2 )

KRISH80 — Mon, 04 May 2020 13:16:58 GMT

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

Re: Calculate and display ratio ( eg 1 : 2 )

Anonymous — Mon, 04 May 2020 13:25:03 GMT

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

Re: Calculate and display ratio ( eg 1 : 2 )

KRISH80 — Mon, 04 May 2020 13:52:46 GMT

Anonymous Thanks a lot

Re: Calculate and display ratio ( eg 1 : 2 )

KRISH80 — Mon, 04 May 2020 13:54:12 GMT

Thanks a lot Greg.