Iterating measure with SUMX

[gambleave]

I cannot get a SUMX measure working for this scenario - would greatly appreciate advice:

I have a measure to count the number of distinct sales from a fact table:

Distinct Sales = CALCULATE(DISTINCTCOUNT(InteractionHistory[Activity ID]), InteractionHistory[Interaction Type] = “Sale”)

Another measure to assess "high volume" status by returning a 1 for 5 or more sales, otherwise a 0.

Volume=if([Distinct Sales]>=5,1,0)

This fact table is connected to a dimension table summarizing employees and a dimension table summarizing customers - the relevant fields in this scenario are:

EmployeeTeams[Employee Name]

Customers[Company Name]

What is the correct formulation of a measure that I can use in a matrix, whereby Company Name displays in rows and the count of employees with "high volume" status (i.e. Volume = 1) displays as the value?

Additionally, how to write a measure that sums these values across all companies. Understand that I can just display the total, but I want to nest in a time intelligence function to chart as a benchmark over time.

Thank you.

[Anonymous]

@gambleave , Try this measure, as it seems to work given the requirements you posted:

High Volume Employee Count = 
    SUMX(
        VALUES('InteractionHistory'[Employee Name]),
        IF([Distinct Sales] >=5, 1, 0)
    )

The test data I used was

Activity ID	Interaction Type	Employee Name	Company Name
1	Sale	Emp1	Comp1
2	Sale	Emp2	Comp2
3	Sale	Emp3	Comp3
4	Sale	Emp3	Comp3
5	Sale	Emp3	Comp3
6	Sale	Emp3	Comp3
7	Sale	Emp3	Comp3
8	Sale	Emp4	Comp4
9	Sale	Emp4	Comp4
10	Sale	Emp4	Comp4
11	Sale	Emp4	Comp4
12	Sale	Emp4	Comp4
13	Sale	Emp4	Comp4
14	Sale	Emp5	Comp4
15	Sale	Emp5	Comp4
16	Sale	Emp5	Comp4
17	Sale	Emp5	Comp4
18	Sale	Emp5	Comp4
19	Sale	Emp5	Comp4
20	Sale	Emp5	Comp4

This is the results in a simple Table visual:

Hope this helps.

[gambleave]

Thanks very much for your help, yes it does seem to work as required 🙂 It seems that the Volume measure is written out here rather than just being referenced. I am wondering if the same approach would work to implement a SUMX for more complex measures (e.g. that nest measures referencing multiple tables in the model)?  Thank you.

[Anonymous]

@gambleave You're right, the Volume measure could be used in the measure I created, like this:

High Volume Employee Count = 
    SUMX(
        VALUES('InteractionHistory'[Employee Name]),
        [Volume]
    )