Formula

[sks2701]

Hi

Im trying to construct a formula for calculating the conversion ratio. So i have a data set containing three years data and also have their sales column. Can someone please help me. thank you

1 year conversion = Count of companies who had sales > 0 in the year after the product launched / Count of companies who had sales< 0 in the year before the product launched
o 2 year conversion = Count of companies who had sales > 0 in any of the 2 years after the product launched / Count of companies who had sales in the year before the product launched
o 3 year conversion = Count of companies who had sales > 0 in any of the 3 years after the product laucnhed / Count of companies who had sales < 0 in the year before the product launched

[v-yingjl · Accepted solution]

Hi @sks2701 ,

You can create measures like this:

1 year conversion =
VAR a =
    CALCULATE (
        COUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            'Table'[Sales] > 0
                && 'Table'[Have launched] = "Yes"
                && 'Table'[Year] = YEAR ( TODAY () )
        )
    )
VAR b =
    CALCULATE (
        COUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            'Table'[Sales] < 0
                && 'Table'[Have launched] = "No"
                && 'Table'[Year] = YEAR ( TODAY () )
        )
    )
RETURN
    a / b

2 year conversion =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            'Table'[Sales] > 0
                && 'Table'[Have launched] = "Yes"
                && 'Table'[Year] <= YEAR ( TODAY () )
                && 'Table'[Year]
                    >= YEAR ( TODAY () ) - 1
        )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            'Table'[Sales] < 0
                && 'Table'[Have launched] = "No"
                && 'Table'[Year] = YEAR ( TODAY () )
        )
    )
RETURN
    a / b

3 year conversion =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            'Table'[Sales] > 0
                && 'Table'[Have launched] = "Yes"
                && 'Table'[Year] <= YEAR ( TODAY () )
                && 'Table'[Year]
                    >= YEAR ( TODAY () ) - 2
        )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            'Table',
            'Table'[Sales] < 0
                && 'Table'[Have launched] = "No"
                && 'Table'[Year] = YEAR ( TODAY () )
        )
    )
RETURN
    a / b

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.