Forum Discussion

gambleave's avatar
gambleave
Icon for Helper II rankHelper 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(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.
 
 
Need Help
Tutorial Requests

3 Replies

  • 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
      Icon for Helper II rankHelper 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]
    )