Counting Rows based on certain conditions and grouping

[mshamsiev]

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 

[Anonymous]

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.

[mshamsiev]

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? 

[v-chuncz-msft]

@mshamsiev,

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

[mshamsiev]

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 

[v-chuncz-msft · Accepted solution]

@mshamsiev,

You could remove the FORMAT function above. Better to learn DAX basics before going ahead.

[mshamsiev]

@v-chuncz-msft,

Thanks again. Working on my DAX basics atm.

Cheers,

Mike