Ratio

[harib]

I have a data like below. i want a ratio for stage wise lead generation.

below formulas i have used 

Opportunity Name Distinct = DISTINCTCOUNT('ReBU Opportunities History Report'[Opportunity Name])

To Stage count = COUNTA('ReBU Opportunities History Report'[To Stage])

ex : tostage/ 18 is a count Opportunity Name Distinct.

but ration is wrong . Appriciate if anyone can help.

[themistoklis]

@harib

What is exactly wrong. The numerator or denominator?

Any example of how the correct values should have been displayed?

[harib]

@themistoklis

Ratio should be come like below . But i have used static values of 18. That's not the correct way. i want it dynamically insead of giving static values of 18.

formula : Ratio = [To Stage count]/18

hope u understand

[themistoklis]

@harib

What is the formula that you use for the denominator?

[harib]

@themistoklis

Opportunity Name Distinct = DISTINCTCOUNT('ReBU Opportunities History Report'[Opportunity Name])

above formlula which i have used. if it's wrong kindly provide the correct solution. My project get struck here

Thanks

[themistoklis]

@harib

Try the followig:

Opportunity Name Distinct = SUMX (
VALUES ( 'ReBU Opportunities History Report'[Owner] ),
CALCULATE ( DISTINCTCOUNT('ReBU Opportunities History Report'[Opportunity Name]) )
)

[harib]

@themistoklis

The which you have provided also giving the same value of 18. but ration is not coming which i have expected. 

[themistoklis]

@harib

Any chance to share the workbook with us?

Mask any sensitive data

[harib]

@themistoklis

Workbook link

https://drive.google.com/open?id=1hlL6bhutQkrRefNlafCFRFz87UtAvpHZ

Ratio should be come like below when you divide To stage count/Opportunity name distinct

formulas which have used :

Opportunity Name Distinct = DISTINCTCOUNT('ReBU Opportunities History Report'[Opportunity Name])

To Stage count = COUNTA('ReBU Opportunities History Report'[To Stage])

 Thanks in advance

[themistoklis]

@harib

Try this formula:

Ratio = COUNTA('Table'[To Stage]) / CALCULATE(DISTINCTCOUNT('Table'[Opportunity Name]), ALLEXCEPT('Table','Table'[Owner]))

[harib · Accepted solution]

@themistoklis

Below formula is working 

Ratio = DIVIDE([To Stage count],'ReBU Opportunities History Report'[Opportunity Name Distinct])-1

Thanks