Sumif equivalents in DAX for calculated columns

[IgorGershenson]

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 System	Sites	Count of Sites	Customer Status	Count of customers	Total Number of Sites in Health System	Total Number of Customers in Health System
UMASS	site 1	1	YES	1	8	5
UMASS	site 2	1	NO	0	8	5
UMASS	site 3	1	YES	1	8	5
UMASS	site 4	1	YES	1	8	5
UMASS	site 5	1	NO	0	8	5
UMASS	site 6	1	YES	1	8	5
UMASS	site 7	1	YES	1	8	5
UMASS	site 8	1	NO	0	8	5
NYU	site A	1	YES	1	4	3
NYU	site B	1	YES	1	4	3
NYU	site C	1	NO	0	4	3
NYU	site D	1	YES	1	4	3

[Nathaniel_C · Accepted solution]

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

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

[AlB · Accepted solution]

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  

[Nathaniel_C · Accepted solution]

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

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