Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX Measure (Variable, etc.)

Hello all! I have data laid out like so

MilestoneApplicantsDate
Lead506/1/2023
Call456/1/2023
Credit35

6/1/2023

Banker306/1/2023
CreditCardApprovl256/1/2023
Close206/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)

ConversionCalculation
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.

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • Manoj_Nair's avatar
    Manoj_Nair
    Solution Supplier
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        Manoj_Nair
        Solution Supplier
        3 years ago

        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.