Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics 365 | Integration, Dataverse... / Calculating average op...
Microsoft Dynamics 365 | Integration, Dataverse...
Suggested answer

Calculating average opportunity revenue and volume per referrer type

(0) ShareShare
ReportReport
Posted on by louisa lane 190

Hi all

We want to show on a dashboard (if possible) the average revenue of opportunities across our system base on a particular type of opportunity (either referrer or the type of work it is we which we show in a bespoke field) and then the average number of opportunities we get based on the referrer (for example accountants, lawyers etc) 

is this possible and if it is, does anyone have a walk through / step by step guide on how to set it up. We have a dashboard where we review and monitor revenue etc so would like to add this data to that dashboard if we can. 

Any suggestions would be appreciated 

Thanks 

  • Suggested answer
    AriaConsulting Profile Picture
    AriaConsulting on at
    RE: Calculating average opportunity revenue and volume per referrer type

    OK, had to start by assuming you may not have the base knowledge.  So, now that's out of the way, I'm going to assume the issue is that you can't create "average" calc columns, correct?  If so, I can think of a few options (roughly in order of cost/complexity):

    1. Create a couple of flows that each contain an expression to calc those averages (which also means that you have to decide if you want simple averages, mean values, weighted averages, etc.). And columns to push those calculated values to.  Once you have the data in the column, you should be able to use that in creating whatever form of visualization you want (that D365 dashboards support, of course).
    2. Use your free Power BI desktop to bring in the data, and create the dashboard as PBI can obviously easily do averages.  Issue here is that you would have to share out the result manually via email as you wouldn't be able to embed it in D365.
    3. Use Excel, running under a user account that has at least system customizer role, to connect to the D365 API, and then Power Query to bring in the tables, create the required calculated columns (which would exist in that Excel file only) such as (for simple average) counting the total number of opportunities, summing the total actual value of the opportunities and dividing that actual total value by the total count. Once you have that file producing the data you want, you can store that in OneDrive and then use a dataflow in the maker portal to extract the final calculated values from Excel and populate some custom fields on the entity that you can then use for the dashboard.
    4. Pay for PBI licenses which would then allow you to embed the dashboard wherever you want
  • louisa lane Profile Picture
    louisa lane 190 on at
    RE: Calculating average opportunity revenue and volume per referrer type

    Thanks for the reply. We have build several dashboards already and know how to use them and create charts, display data in different way etc. The issue we are having is now they want to work out and show the average revenue across all of our opportunities and if there was a way to work out and show the average referrals from a particular client type such as from lawyers, accountants etc so we can work out how many opportunities we should on average be getting from that client type. Thanks for your suggestion though.. i'll keep searching.

  • AriaConsulting Profile Picture
    AriaConsulting on at
    RE: Calculating average opportunity revenue and volume per referrer type

    OK, then you will need to do it in the native D365 PowerApps designer.  I suggest starting by reading this: docs.microsoft.com/.../start-your-day-dashboard-chart. and if you run into issues pop back here with any specific info on the issues and we can try to give you a hand.

  • louisa lane Profile Picture
    louisa lane 190 on at
    RE: Calculating average opportunity revenue and volume per referrer type

    Hi, I have the free Power BI licence.

    Thanks

  • AriaConsulting Profile Picture
    AriaConsulting on at
    RE: Calculating average opportunity revenue and volume per referrer type

    Hi louisa lane,

    The first question is whether you have Power BI licenses or not as there are obviously differences.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Get batch job history log from D365 FO to Power Automate

Product updates

Dynamics 365 release plans