Average percentage of many grouped percentages

[leodm]

Database sample:

Date	Squad	Failed?
21/07/2022 22:00:00	Squad_1	Yes
27/07/2022 23:00:00	Squad_1	No
18/08/2022 23:00:00	Squad_1	No
04/08/2022 22:00:00	Squad_2	No
22/08/2022 23:00:00	Squad_2	No
17/08/2022 23:00:00	Squad_2	No
25/08/2022 23:00:00	Squad_3	Yes
25/08/2022 19:00:00	Squad_3	No
31/08/2022 22:00:00	Squad_3	Yes
25/08/2022 23:00:00	Squad_4	Yes
25/08/2022 19:00:00	Squad_4	Yes
31/08/2022 22:00:00	Squad_4	Yes

What I need to calculate:

I must use only DAX MEASURES because values will change according to filters.

I need to show the Expected Result value on a Card visual, which is the average of the "Rate of Failure per Squad (%)" column.

Thank you in advance!

[MohammadLoran25 · Accepted solution]

Hi,

By Creating these 2 measures, you can get your desired result:

First Measure as Below

Rate of Failure Per Squad =
VAR _Squad =
    SELECTEDVALUE ( SquadResults[Squad] )
RETURN
    DIVIDE (
        COUNTROWS (
            FILTER (
                ALL ( SquadResults ),
                SquadResults[Squad] = _Squad
                    && SquadResults[Failed?] = "Yes"
            )
        ) + 0,
        COUNTROWS ( FILTER ( ALL ( SquadResults ), SquadResults[Squad] = _Squad ) )
    )

Then with creating this measure you can put it in the card visual:

ExpectedResult =
AVERAGEX ( VALUES ( SquadResults[Squad] ), [Rate of Failure Per Squad] )

Regards,

Loran