cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IgorGershenson Frequent Visitor
Frequent Visitor

Sumif equivalents in DAX for calculated columns

I want to create two calculated columns in my data. I want the first one to show the number of sites for each hospital system and the second to show the total number clients in that system. Here is a simple example below. In Excel I would use Sumif to create the last two columns but how do I do it using DAX for calculated columns. I need these columns to create additional columns and measures later on.  Please help.

Health SystemSitesCount of SitesCustomer StatusCount of customersTotal Number of Sites in Health SystemTotal Number of Customers in Health System
UMASSsite 11YES185
UMASSsite 21NO085
UMASSsite 31YES185
UMASSsite 41YES185
UMASSsite 51NO085
UMASSsite 61YES185
UMASSsite 71YES185
UMASSsite 81NO085
NYUsite A1YES143
NYUsite B1YES143
NYUsite C1NO043
NYUsite D1YES143
2 ACCEPTED SOLUTIONS

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Sumif equivalents in DAX for calculated columns

Tot Num Customer = CALCULATE(COUNT(Health[Customer Status]),ALLEXCEPT(Health,Health[Health System]),Health[Customer Status] = "YES")



Tot Num of Sites = CALCULATE(Count(Health[Health System]),ALLEXCEPT(Health,Health[Health System]))

Tot num.PNG

Hi @IgorGershenson ,

I did these as measures, so you can drop them on the visual.  They match what you have posted.  Thanks for the clarity in your posting! The CALCULATE allows this measure to iterate through the whole table, and the ALLEXCEPT keeps the one variable the same. 

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

Highlighted
Super User
Super User

Re: Sumif equivalents in DAX for calculated columns

Hi @IgorGershenson 

 

Total Number of Sites in Health System =
CALCULATE (
    DISTINCTCOUNT ( Table1[ Sites] ),
    ALLEXCEPT ( Table1, Table1[Health System] )
)

 

Total Number of Customers in Health System =
CALCULATE (
    SUM ( Table1[ Count of Customers] ),
    ALLEXCEPT ( Table1, Table1[Health System] )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

View solution in original post

2 REPLIES 2
Nathaniel_C Super Contributor
Super Contributor

Re: Sumif equivalents in DAX for calculated columns

Tot Num Customer = CALCULATE(COUNT(Health[Customer Status]),ALLEXCEPT(Health,Health[Health System]),Health[Customer Status] = "YES")



Tot Num of Sites = CALCULATE(Count(Health[Health System]),ALLEXCEPT(Health,Health[Health System]))

Tot num.PNG

Hi @IgorGershenson ,

I did these as measures, so you can drop them on the visual.  They match what you have posted.  Thanks for the clarity in your posting! The CALCULATE allows this measure to iterate through the whole table, and the ALLEXCEPT keeps the one variable the same. 

 


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

Highlighted
Super User
Super User

Re: Sumif equivalents in DAX for calculated columns

Hi @IgorGershenson 

 

Total Number of Sites in Health System =
CALCULATE (
    DISTINCTCOUNT ( Table1[ Sites] ),
    ALLEXCEPT ( Table1, Table1[Health System] )
)

 

Total Number of Customers in Health System =
CALCULATE (
    SUM ( Table1[ Count of Customers] ),
    ALLEXCEPT ( Table1, Table1[Health System] )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 80 members 1,317 guests
Please welcome our newest community members: