topic Re: DAX Measure to show the total percentage of opportunities won for the associated lead source? in DAX Commands and Tips

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

kajelliott — Mon, 26 Feb 2024 23:12:07 GMT

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 🙂

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

Anonymous — Tue, 27 Feb 2024 01:49:26 GMT

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.

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

kajelliott — Tue, 27 Feb 2024 04:19:10 GMT

Hi Anonymous

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

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

Anonymous — Tue, 27 Feb 2024 08:14:48 GMT

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

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

kajelliott — Tue, 27 Feb 2024 22:37:22 GMT

Hi Anonymous

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.

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

Anonymous — Wed, 28 Feb 2024 09:21:52 GMT

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.

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

kajelliott — Wed, 28 Feb 2024 22:26:52 GMT

Hi Anonymous

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