Forum Discussion

gambleave's avatar
gambleave
Helper II
4 years ago

Iterating measure with SUMX

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(DIST...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 IDInteraction TypeEmployee NameCompany Name
1SaleEmp1Comp1
2SaleEmp2Comp2
3SaleEmp3Comp3
4SaleEmp3Comp3
5SaleEmp3Comp3
6SaleEmp3Comp3
7SaleEmp3Comp3
8SaleEmp4Comp4
9SaleEmp4Comp4
10SaleEmp4Comp4
11SaleEmp4Comp4
12SaleEmp4Comp4
13SaleEmp4Comp4
14SaleEmp5Comp4
15SaleEmp5Comp4
16SaleEmp5Comp4
17SaleEmp5Comp4
18SaleEmp5Comp4
19SaleEmp5Comp4
20SaleEmp5Comp4

 

This is the results in a simple Table visual:

 

Hope this helps.

gambleave's avatar
gambleave
Helper II
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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]
    )