Forum Discussion
Counting Rows based on certain conditions and grouping
Hi all,
I'm trying to do a 'countifs' type calc.
Essentially I have a list of assignment groups with multiple appearances within one column, and an 'Achieved' or 'Breached' status (as seen below). I wan't to determine the % achieved by assignment groups. That way I can hopefully create a KPI visual that will filter based on the selected assignment group or across the firm without filters.
Any help will be much appreciated.
Example:
Assg Group Status
VC Achieved
VC Achieved
CRM Breached
CRM Achieved
-Mike
You could remove the FORMAT function above. Better to learn DAX basics before going ahead.
6 Replies
Try creating this measure
Breached Count = CALCULATE( DISTINCTCOUNT('Your Table'[Assg Group]), 'YourTable'[Status] = "Breached" )This will give you a list of all of the Assg Groups that have at least 1 breached.
From here, if you do a distinct count of the entire list you'll be able to detiremine how many groups you have, then you can work backwards and figure out how many groups do not have breached.Hi Anonymous,
Thanks mate! I can see where you're going with the idea. But would that allow me to calculate Achieved % based on each individual assignment group?
You may refer to the following DAX that creates a new table.
Table = SUMMARIZE ( Table1, Table1[Assg Group], "Achieved %", FORMAT ( COUNTROWS ( FILTER ( Table1, Table1[Status] = "Achieved" ) ) / COUNTROWS ( Table1 ), "Percent" ) )
