DAX Measure (Variable, etc.)

[Anonymous]

Hello all! I have data laid out like so

Milestone	Applicants	Date
Lead	50	6/1/2023
Call	45	6/1/2023
Credit	35	6/1/2023
Banker	30	6/1/2023
CreditCardApprovl	25	6/1/2023
Close	20	6/1/2023

The goal is for me to write a DAX measure which will show the Lead Milestone Conversion for these applicants. The desired outcome would be the conversion (and I included how it'd be calculated next to it)

Conversion	Calculation
100%	50/50
90%	45/50
70%	35/50
60%	30/50
50%	25/50
40%	20/50

I am kind of torn on the DAX syntax to use. I'm thinking designating a variable to signify that we divide every milestone by the Lead totals would make sense, but I am torn.

[Manoj_Nair]

@Anonymous @ Try this and let me know if this works.

Lead Milestone Conversion = 
DIVIDE(
    CALCULATE(
        SUM('Table'[Applicants]),
        'Table'[Milestone] = "Lead"
    ),
    CALCULATE(
        SUM('Table'[Applicants]),
        'Table'[Milestone] = "Lead" || 'Table'[Milestone] = "Call" || 'Table'[Milestone] = "Credit" || 'Table'[Milestone] = "Banker" || 'Table'[Milestone] = "CreditCardApprovl" || 'Table'[Milestone] = "Close"
    )
)

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[Anonymous]

Hello!

So I got it to work! However for some reason it does not with my date slice

TESTING CONVERSION METRIC = 
DIVIDE(
    CALCULATE(
        SUM('Table'[AllIn]),
        ALLEXCEPT('Table',
        'Table'[Milestone])
    ),
    CALCULATE(
        'Table'[Leads],
        ALLEXCEPT('Table', 'Table'[Milestone]),
        ALL('Table'[Milestone]
    )))

[Manoj_Nair]

@Anonymous- Try this one.

TESTING CONVERSION METRIC = 
VAR SelectedDate = MAX('Table'[Date])   // Get the selected date from the slicer
RETURN
DIVIDE(
    CALCULATE(
        SUM('Table'[AllIn]),
        ALLEXCEPT('Table', 'Table'[Milestone]),
        'Table'[Date] = SelectedDate   // Apply the selected date filter
    ),
    CALCULATE(
        'Table'[Leads],
        ALLEXCEPT('Table', 'Table'[Milestone]),
        'Table'[Date] = SelectedDate,   // Apply the selected date filter
        ALL('Table'[Milestone])
    )
)

I updated the measure. Uses the MAX function to retrieve the selected date from the slicer and assigns it to the variable SelectedDate. Then, in both CALCULATE functions, the condition 'Table'[Date] = SelectedDate is added to filter the data based on the selected date.