Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 ) )
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]) )
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 )
Hi Zubair, thanks for replying! Is the formula above supposed to return a table?
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 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.
Hi @Phil_Seamark, thanks for replying. I was wondering what's the +0 for in the formula? This seems to be neat solution
HI @Anonymous,
The +0 just converts a blank cell to a 0 in this case. Take it out and see. 🙂
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!
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?
@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 ) )
Yes, I need a calculated table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |