How to summarize based on success / failure

[murali5431]

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!

[harshnathani]

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)

[murali5431]

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

[harshnathani]

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)

[harshnathani]

Hi @murali5431 ,

Did the previous solution solve your issue.

If yes, please mark it as Solution for other users to benefit.

Regards,

HN

[mahoneypat]

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

[lbendlin]

Divide the distinct count of all entries that have a No by the distinct count of all entries. Then subtract that from 1.