How to get average from a measure in Power BI desk

问题:

In my data source I have column Quoted and Submitted.

Dividing the Sum(Quoted) by SUM(Submitted) gives us Sub to Quote ratio.

So I have created a measure that divides Quoted/Submitted.

Sub to Quote = DIVIDE([Total Quoted], [Total Submitted])

Now, how can I simply have Average of that ratio?

So instead of 9%, I need to see 8.18%

.PBIX file can be accessed here:

https://www.dropbox.com/s/ug932qjju7zahfs/ClearanceDateNewWithCatalytic1.pbix?dl=0

回答1:

I would try something like this:

Sub to Quote =
AVERAGEX(
    SUMMARIZE(dim_Date,
        dim_Date[Qtr Year],
        "Average", DIVIDE( [Total Quoted], [Total Submitted] )
    ),
    [Average]
)

This summarizes your table to get the average for each quarter and then averages those averages.

Note: This returns 8.15% rather than 8.18%. I'm guessing this is just a rounding difference though.