Skip to main content
cancel
Showing results for
Search instead 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,
Adamk Kong

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,
Adamk Kong

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,
Adamk Kong

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,
Adamk Kong

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 🙂

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors