Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
You could remove the FORMAT function above. Better to learn DAX basics before going ahead.
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" ) )
Hey @v-chuncz-msft,
Worked a treat!! Thanks 🙂 Only issue is the resulting percentages are showing as txt and I cannot convert them to a numberical %.
I was hoping to filter these SLA's by priorities 1-4 as each row has an assigned priority status. However, the SLA priorities are listed on the original SLA table (table1).
Cheers,
Mike
You could remove the FORMAT function above. Better to learn DAX basics before going ahead.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |