cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IamTDR
Helper V
Helper V

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

Capture.JPG

1 ACCEPTED SOLUTION

Hi @IamTDR ,

 

Take a try of this :

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

6.PNG

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

5 REPLIES 5
Mariusz
Community Champion
Community Champion

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



parry2k
Super User
Super User

@IamTDR try following 

 

Sum Enrollment = 
SUMX(
SUMMARIZECOLUMNS( Table[Territory], Table[ProgCode]),
CALCULATE( SUM( Table[Enrollment] ) )
)





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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.

 

 

Capture.JPG

 

Measure currently being used.

Capture2.JPG

Hi @IamTDR ,

 

Take a try of this :

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

6.PNG

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Thanks!  Appreicate the aid

TDR

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.