Calculating Percentage using Groupby

[Anonymous]

I need to make a table with the percentage of compliance per person. Percentage of compliance is equivalent to the number of YES over the number of rows related to a particular person. How am I suppose to compute this using DAX? I have the idea to use GROUPBY but I don't exactly know how to do it. The data kind of looks like this: 

 Thanks!

[Zubair_Muhammad · Accepted solution]

@Anonymous

If you directly want a calculated table as an OUTPUT,,, then goto Modelling Tab>>New Table and use this formula

Table =
SUMMARIZE (
    TableName,
    TableName[Person],
    "%age of compliance",
    VAR totalCountforaperson =
        CALCULATE (
            COUNT ( TableName[Person] ),
            ALLEXCEPT ( TableName, TableName[Person] )
        )
    VAR CountwithYes =
        CALCULATE (
            COUNT ( TableName[Person] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[Person] ),
                TableName[Compliance] = "Yes"
            )
        )
    RETURN
        DIVIDE ( CountwithYes, totalCountforaperson )
)

[Zubair_Muhammad]

HI @Anonymous

Try this

=
VAR totalCountforaperson =
    CALCULATE (
        COUNT ( TableName[Person] ),
        ALLEXCEPT ( TableName, TableName[Person] )
    )
VAR CountwithYes =
    CALCULATE (
        COUNT ( TableName[Person] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Person] ),
            TableName[Compliance] = "Yes"
        )
    )
RETURN
    DIVIDE ( CountwithYes, totalCountforaperson )

[Anonymous]

Hi Zubair, thanks for replying! Is the formula above supposed to return a table?

[Phil_Seamark · Accepted solution]

Hi @Anonymous

The below calculated table will gget you pretty close

New Table = 
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            'Table1'[Person],
            "Rows Total",COUNTROWS('Table1') ,
            "Compliant",COUNTROWS(Filter('Table1','Table1'[Compliance]="Yes"))+0
            ),
            "Ratio" , DIVIDE([Compliant],[Rows Total])
            )

[Anonymous]

@Phil_Seamark I do have one question, though. Do you know if there's a way to accomplish this using groupby and filtering? I'm used to solving this kind of stuff using the groupby method of Pandas in Python, I'm just wondering if there's a same way in DAX.

[Anonymous]

Hi @Phil_Seamark, thanks for replying. I was wondering what's the +0 for in the formula? This seems to be neat solution 

[Phil_Seamark]

HI @Anonymous,

The +0 just converts a blank cell to a 0 in this case.  Take it out and see. 🙂

[Anonymous]

Hi @Phil_Seamark, this really works well! I'm actually a beginner in DAX and this technique is really helpful for me specially for future use. Thanks! 

[Zubair_Muhammad]

Hi,

No. Its a MEASURE.

If you put it in a TABLE VISUAL alongwith Names of Persons you will get the desired percentages

Do you need a calcuated table?

[Zubair_Muhammad · Accepted solution]

@Anonymous

If you directly want a calculated table as an OUTPUT,,, then goto Modelling Tab>>New Table and use this formula

Table =
SUMMARIZE (
    TableName,
    TableName[Person],
    "%age of compliance",
    VAR totalCountforaperson =
        CALCULATE (
            COUNT ( TableName[Person] ),
            ALLEXCEPT ( TableName, TableName[Person] )
        )
    VAR CountwithYes =
        CALCULATE (
            COUNT ( TableName[Person] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[Person] ),
                TableName[Compliance] = "Yes"
            )
        )
    RETURN
        DIVIDE ( CountwithYes, totalCountforaperson )
)

[Anonymous]

Thank you so much @Zubair_Muhammad! I got the desired table 

[Anonymous]

Yes, I need a calculated table.