cancel
Showing results 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

Resolver I

## Calculating an average number of services sold from a meaure

Hi

From a table of sales data i'm trying to calculate a value for Average Number of Services sold to a group of customers that can be tracked as a KPI on a monthly basis.

I have data structured in the following way:

From this i can see that my customer with Account ID 1000 is consuming 3 services in July vs 2 services in June.  I can calculate this across the customer base with a measure:

Number of Services = CALCULATE(DISTINCTCOUNT(Sales[Service]),Sales[Revenue]>0)

to ensure that i'm only counting services that haven't been refunded/cancelled on a separate line.

What i then need to achieve is an Average of the Number of services sold which i can then categorise by Sales Teritory and whether the customer is flagged as High Value etc, so from above Account 1000 = 3 services, 2000 = 2 services, 3000 = 1 service and 4000 = 1 service for July to give an average of 1.75

Once calculated i would then track performance against this on a month over month basis and drilling down by sales territory etc.

I've tried a few variants of similar proposals i've seen in other threads and can't seem to get anything working - would anyone be able to point me in the right direction?

Many thanks in advance for any help

1 ACCEPTED SOLUTION
Resolver I

Think i've got to the answer myself, for reference have used:

Average Number of Services =

VAR __Table =

SUMMARIZE(

'Sales' ,Sales[Account ID],

"Measure",[Number of Services]

)

RETURN

AVERAGEX(__Table,[Number of Services])

Resolver I

Think i've got to the answer myself, for reference have used:

Average Number of Services =

VAR __Table =

SUMMARIZE(

'Sales' ,Sales[Account ID],

"Measure",[Number of Services]

)

RETURN

AVERAGEX(__Table,[Number of Services])

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.