Count and percentage of a subcategory of a category

[mousumimandal]

I have these data in an excel

Priority	SLA Breached	Number
P1	Yes	INC0000
P2	No	INC0001
P3	Yes	INC0002
P3	No	INC0003
P4	No	INC0004
P4	No	INC0005
P5	Yes	INC0006
P5	Yes	INC0007

and I want output as below in power BI table format. Any help will be appreciated

Priority	Total tickets	% of No
P1	1	0
P2	1	100
P3	2	50
P4	2	100
P5	2	0

[barritown · Accepted solution]

Hi @mousumimandal,

You can try this option:

DAX code in plain text:

Result Table = 
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', [Priority], "Total tickets", CALCULATE ( COUNTROWS ( 'Table' ) ) ),
        "% of No", 
        VAR currentPriority = [Priority]
        RETURN FORMAT ( ( COUNTROWS ( FILTER ( 'Table', [SLA Breached] = "No" && [Priority] = currentPriority ) ) + 0 ) / [Total tickets], "Percent" ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown · Accepted solution]

Hi @mousumimandal,

You can try this option:

DAX code in plain text:

Result Table = 
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', [Priority], "Total tickets", CALCULATE ( COUNTROWS ( 'Table' ) ) ),
        "% of No", 
        VAR currentPriority = [Priority]
        RETURN FORMAT ( ( COUNTROWS ( FILTER ( 'Table', [SLA Breached] = "No" && [Priority] = currentPriority ) ) + 0 ) / [Total tickets], "Percent" ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[mousumimandal]

Thank you, It worked. There was another thing
I was trying to change the background colour of "% of No". but its not showing me generic option like this.

Instead, I am getting count, sum etc.

[barritown · Accepted solution]

Hi @mousumimandal,

Try removing the FORMAT (...) part and updating settings (see the screenshot below). Should work after that.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[mousumimandal]

Thanks. It worked😀