cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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 🙂

1 ACCEPTED SOLUTION
Community Support

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,

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

6 REPLIES 6
Community Support

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,

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

Helper I

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

Community Support

Hi @kajelliott ,

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

Best Regards,

Helper I

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.

Community Support

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,

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

Helper I

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