Determine average quote amount for a sales opportunity

[cyberninja]

Pardon me, I am just getting started with Power BI.  

All within the same table, I have an OrderQuoteID which is unique.  Some OpportunityIds have multiple quotes underneath each.  I would like to total the Quote Amounts for a particular Opportunity, then divide by the number of quotes for that Opportunity, so I can determine what the average quote is for every Opportunity.  Data example below -

This is what I have now -

Table: vSummaryQuote

Quot_OrderQuoteID	Oppo_OpportunityId	Quot_isquote	Quot_Amount
1	11111	1	1000
2	11111	1	1500
3	22222	1	5000
4	33333	1	10000
5	44444	1	100
6	44444	1	200
7	44444	1	300

Based on the data above, I would need to be able to return a column similar to below to make a simple bar graph -

Oppo_OpportunityId	AvgQuotePerOpportunity
11111	1250
22222	5000
33333	10000
44444	200

Thoughts on how I can accomplish this?

Much thanks!

[cyberninja]

To re-phrase my question, would it be sufficient to use the AVERAGE function within the context of a particular OpportunityID?  How might I accomplish this?  thx

[v-huizhn-msft · Accepted solution]

Hi @cyberninja,

Using AVERAGE function is not efficient. Because AVERAGE function calculates the arithmetic mean of the numbers in the column itself. In your scenario, you want to get average quote for every Opportunity based on Quot_isquote and Quote Amounts, there are in different columns.

You should create the measures to calculate the count of Quotes, the sum of amount. Then calculate average for every Opportunity as follows.

Count = CALCULATE(COUNT(Test1[Quot_isquote]),ALLEXCEPT(Test1,Test1[Oppo_OpportunityId]))
Total amonut = CALCULATE(SUM(Test1[Quot_Amount]),ALLEXCEPT(Test1,Test1[Oppo_OpportunityId]))
Average1 = CALCULATE(SUM(Test1[Quot_Amount]),ALLEXCEPT(Test1,Test1[Oppo_OpportunityId]))/CALCULATE(COUNT(Test1[Quot_isquote]),ALLEXCEPT(Test1,Test1[Oppo_OpportunityId]))

Create a table, select the Opportunity and measure as values, you will get the desired result.

Finally, you create a Clustered column chart, select the OpportunityId as Axis, the Average1 as Value level. You will get a simple bar graph.

If you have any question, please feel free to ask.

Best Regards,
Angelia