Count results with dynamic filtering

[phelms]

I am trying to count how many agent that is on target at least 75% of the time in percentage of total number of agents on work in the same period, and show that on a single card. 

In a table, that is pretty straight forward to calculate per agent, using this measure: SUM([on target]) / SUM([at work]), but I can not seem to figure out how to get the total percentage of agents that were on target at least 75% of the time, to show.

Also, I want the calculation to be dynamic, so that I can change the week range in a slicer to adjust the result accordingly.

What am I missing?

Data table:

fullname	week	on target	off target	at work
Agent1	12	1	0	1
Agent2	7	1	0	1
Agent2	11	0	1	1
Agent2	10	0	1	1
Agent2	9	0	1	1
Agent2	8	0	1	1
Agent2	5	0	1	1
Agent2	3	0	1	1
Agent2	1	0	1	1
Agent2	4	0	1	1
Agent2	2	0	1	1
Agent3	3	1	0	1
Agent3	2	1	0	1
Agent3	5	1	0	1
Agent3	4	1	0	1
Agent4	3	1	0	1
Agent4	2	1	0	1
Agent4	1	1	0	1
Agent4	5	1	0	1
Agent4	7	1	0	1
Agent4	4	1	0	1
Agent4	13	0	1	1
Agent4	12	0	1	1
Agent4	11	0	1	1
Agent4	10	0	1	1
Agent4	9	0	1	1
Agent4	6	0	1	1
Agent5	14	1	0	1
Agent5	17	1	0	1
Agent5	18	1	0	1
Agent5	15	1	0	1
Agent5	16	1	0	1
Agent5	13	0	1	1
Agent5	19	0	1	1
Agent6	3	1	0	1
Agent6	2	1	0	1
Agent6	1	1	0	1
Agent6	5	1	0	1
Agent6	12	1	0	1
Agent6	11	1	0	1
Agent6	14	1	0	1
Agent6	8	1	0	1
Agent6	15	1	0	1
Agent6	16	1	0	1
Agent6	4	1	0	1
Agent6	6	1	0	1
Agent6	3	1	0	1
Agent6	2	1	0	1
Agent6	5	1	0	1
Agent6	6	1	0	1
Agent6	9	0	1	1
Agent6	8	0	1	1
Agent6	1	0	1	1
Agent7	5	1	0	1
Agent7	13	0	1	1
Agent7	18	0	1	1
Agent7	14	0	1	1
Agent7	17	0	1	1
Agent7	19	0	1	1
Agent7	12	0	1	1
Agent7	11	0	1	1
Agent7	16	0	1	1
Agent7	15	0	1	1
Agent7	7	0	1	1
Agent7	10	0	1	1
Agent7	9	0	1	1
Agent7	8	0	1	1
Agent7	3	0	1	1
Agent7	1	0	1	1
Agent7	6	0	1	1
Agent7	4	0	1	1
Agent7	2	0	1	1

[v-shex-msft]

HI @phelms,

You can try to use below measure to calculate on target percent.

Formula:

On target Percent =
CALCULATE (
    SUM ( 'Sample'[on target] ) / SUM ( 'Sample'[at work] ),
    ALLSELECTED ( 'Sample' ),
    VALUES ( 'Sample'[fullname] ),
    VALUES ( 'Sample'[week] )
)

Result:

Regards,

Xiaoxin Sheng

[phelms]

Almost there. 

What I want to do, is to count how many agents were on target 100% as a percent of whole (in this example 2/7 = 28.6%), and how many agents were on target at least 75% of the time (3/7 = 42.9%).

Can I do that?

[v-shex-msft · Accepted solution]

HI @phelms,

You can use below formula to get the total percent with conditions:

100% / 75% Taget Percent =
VAR temp =
    SUMMARIZE (
        'Sample',
        [fullname],
        "Pecent", CALCULATE (
            SUM ( 'Sample'[on target] ) / SUM ( 'Sample'[at work] ),
            ALLSELECTED ( 'Sample' ),
            VALUES ( 'Sample'[fullname] ),
            VALUES ( 'Sample'[week] )
        )
    )
RETURN
    FORMAT (
        COUNTROWS ( FILTER ( temp, [Pecent] = 1 ) )
            / COUNTROWS ( VALUES ( 'Sample'[fullname] ) ),
        "Percent"
    )
        & " / "
        & FORMAT (
            COUNTROWS ( FILTER ( temp, [Pecent] > 0.75 ) )
                / COUNTROWS ( VALUES ( 'Sample'[fullname] ) ),
            "Percent"
        )

Regards,

Xiaoxin Sheng

[phelms]

Worked like a charm.

Thank you very much.