Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello,
I am working on a dataset that has entries as below.
My objective is to calcluate the % of successful entries / total number of entries.
Any entry is considered successful only if all rows corresponding to the Entry number show "Y". Even 1 "N" against an entry number makes it a failure. Please assist with a DAX measure to calculate the percentage.
For below example AA2, AA3 & AA5 are considered successful based on above logic and hence the result should be 3/5 - 60%
Entry Number | Success / Failure |
AA1 | Y |
AA1 | N |
AA2 | Y |
AA3 | Y |
AA3 | Y |
AA4 | N |
AA4 | Y |
AA5 | Y |
AA5 | Y |
Thanks for the help!
Hi @murali5431 ,
You can try this as a measure.
Results =
VAR failuretable =
SUMMARIZE (
'Table',
'Table'[Entry Number],
"Failure", CALCULATE (
MAX ( 'Table'[Success / Failure] ),
'Table'[Success / Failure] = "N"
)
)
VAR finaltable =
COUNTROWS (
EXCEPT (
failuretable,
'Table'
)
)
VAR distinctrowsinmaintable =
DISTINCTCOUNT ( 'Table'[Entry Number] )
RETURN
DIVIDE (
finaltable,
distinctrowsinmaintable
)
Pull the measure onto a card for your visualization.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @harshnathani ,
Thanks for the response!
My table actually has around 25 columns of data. I had shared two columns as these are the only two that matter for the calclualton. This is probably causing an error "Each table argument of 'EXCEPT' must have the same number of columns.".
Could you advise a workaround for this?
Thanks,
Murali
Hi @murali5431 ,
Try this
Results =
VAR failuretable =
SUMMARIZE (
'Table4',
Table4[Entry Number],
"Failure", CALCULATE (
MAX ( Table4[Success / Failure] ),
'Table4'[Success / Failure] = "N"
)
)
VAR fin =
SUMMARIZE (
Table4,
Table4[Entry Number],
Table4[Success / Failure]
)
VAR finaltable =
COUNTROWS (
EXCEPT (
failuretable,
fin
)
)
VAR distinctrowsinmaintable =
DISTINCTCOUNT ( 'Table4'[Entry Number] )
RETURN
DIVIDE (
finaltable,
distinctrowsinmaintable
)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @murali5431 ,
Did the previous solution solve your issue.
If yes, please mark it as Solution for other users to benefit.
Regards,
HN
Please try this expression that returns 60% for your example data.
Pct Successful =
VAR __summarytable =
ADDCOLUMNS (
VALUES ( Entry[Entry Number] ),
"@N Count", CALCULATE ( COUNTROWS ( Entry ), Entry[Success / Failure] = "N" ) + 0
)
RETURN
DIVIDE (
COUNTROWS ( FILTER ( __summarytable, [@N Count] = 0 ) ),
COUNTROWS ( __summarytable )
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Divide the distinct count of all entries that have a No by the distinct count of all entries. Then subtract that from 1.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
8 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
12 | |
11 | |
9 | |
9 |