Forum Discussion

IamTDR's avatar
IamTDR
Responsive Resident
7 years ago
Solved

Help With a Sumx Measure

Morning

I am working with a single table that has 'enrollment' numbers that I would like to sum up.  The trick is, each each territory/program has a distinct enrollment number.  I cannot simply sum up the enrollment column for the table has duplicate territory/program combinations.

I was trying to use the DAX formula 'Enrollment=sumx(Enrollment_tbl,enrollment).  This did not work as I expected.

 

I am attaching an image of data from an excel spreadsheet to better demonstrate what I am trying to achieve in BI.

Thanks in advance.

TDR

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi IamTDR ,

     

    Take a try of this :

    Measure = SUMX(GROUPBY('BI BookInUse',[Program],[Territory],''BI BookInUse''[Enrollment]),[Enrollment])

    Best regards,

    Dina Ye

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    IamTDR try following 

     

    Sum Enrollment = 
SUMX(
SUMMARIZECOLUMNS( Table[Territory], Table[ProgCode]),
CALCULATE( SUM( Table[Enrollment] ) )
)
    • IamTDR's avatar
      IamTDR
      Responsive Resident
      7 years ago

      Thanks for the suggestions but so far I am not receiving the desired results.  I inserted an image of what my visual currently looks like.  The Total Enrollment number should total to 12,144 and not 60,720.  My Total Enrollment number is based on Territory and Program.

       

       

       

      Measure currently being used.

      • v-diye-msft's avatar
        v-diye-msft
        Community Support
        7 years ago

        Hi IamTDR ,

         

        Take a try of this :

        Measure = SUMX(GROUPBY('BI BookInUse',[Program],[Territory],''BI BookInUse''[Enrollment]),[Enrollment])

        Best regards,

        Dina Ye

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi IamTDR 

    The below should work for you.

     

    Enrollment Measure = 
SUMX(
    GROUPBY( Enrollment_tbl, Enrollment_tbl[Territory], Enrollment_tbl[ProgCode] ),
    [Enrollment]
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski