DAX Measure to show the total percentage of opportunities won for the associated lead source?

[kajelliott]

Hi All, 

I have a column of lead sources in my data table and have calculated the percentage of opportunties won with a DAX calculation which results in this table.

Lead Source	Total % Won
A	22%
B	24%
C	70%
D	13%
E	17%

How do I create a Dax expression so that this percentage will display in a table against the opportunity like so?

Opportunity	Lead Source	Lead Source Won
Name A	E	17%
Name B	D	13%
Name C	A	22%
Name D	B	24%

Opportunity and Lead Source exist in the same data table in power BI
The Total % Won calculation is DIVIDE([Total Opps for Won], [Total Opps]) + 0

Any help is appreciated! Thank you 🙂

[v-kongfanf-msft]

Hi @kajelliott ,

Based on your description, you could create a data table similar to the following and then apply this formula.

PercentageWon = 
VAR totalOpps =
    CALCULATE ( SUM ( 'Table'[Total Opps] ) )
VAR wonOpps =
    CALCULATE ( SUM ( 'Table'[Total Opps for won] ) )
RETURN
    DIVIDE ( wonOpps, totalOpps ) + 0

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kajelliott]

Hi @v-kongfanf-msft 

Thanks for your suggestion. Both [Total Opps} & [Total Opps for Won] are measures, so it will not allow that for the expression you have written. 

Also, I notice that the expression does not reference the Lead Source at all, so i'm not sure how this would work. It looks like the expression would return the percentage won for each opportunity, which will either be 100% or 0% depending on the opportunity. 

Let me know what you think.

Thanks!

Kayla

[v-kongfanf-msft]

Hi @kajelliott ,

I don't quite understand what you need, please provide some test data and screenshot information and explain it.

Best Regards,
Adamk Kong

[kajelliott]

Hi @v-kongfanf-msft 

Sorry, it wont let me upload a pbix file which is frustrating. To give you an idea, my data looks like this: 

I then create measures to return the percentage of opportunies won which results in this 

What I need is a measure that returns the percentage of opportunities won for the lead source of each opportunity. Eg, Opportunities with a lead source 'Website' are won 80% of the time.

Example below - 

Hopefully this helps!

Thank you. 

[v-kongfanf-msft · Accepted solution]

Hi @kajelliott ,

You can try below formula:

M_ =
VAR total_ =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Load Source] = MAX ( 'Table'[Load Source] ) )
    )
VAR cur_ =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Load Source] = MAX ( 'Table'[Load Source] )
                && 'Table'[Status] = "won"
        )
    )
RETURN
    IF ( ISBLANK ( DIVIDE ( cur_, total_ ) ), 0, DIVIDE ( cur_, total_ ) )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kajelliott]

Hi @v-kongfanf-msft 

This is perfect! Thank you so much for your time and effort. It is greatly appreciated 🙂