Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculating Percentage using Groupby

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 suppo...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

     

     

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

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

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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.